jaman posted

select + delete in single query , how to ?

i am managing to code a delete query so i can delete some selected entries form table which got from another select query .
situation like this
i want to delete all the common value contained row for a particular field .

so i asked for help

and i got that i can write it by a query where i can compare same field 's value to same field's other value in same table . query is as below


SELECT a.name, a.id AS idmain, b.id AS idsecond
FROM `users` AS a, `users` AS b
WHERE a.name = b.name
AND a.id <> b.id
AND a.id < b.id
order by a.id

it give me all rows which have same value for users(tablename) 's name (field)

now i want to delete it which most recent . want to keep old one .
i know i can do it with separate query but i want to do it with single query where delete + above select query
is that possible to join delete and other select so that we can use select query 's result to delete row ?

Edit Question

Mitul Dabhi
answered Nov 30 '-1 00:00

its easy to merge delete and select query .


delete form tablename where id in (select id from tablename)


delete from users where id in(
SELECT a.name, a.id AS idmain, b.id AS idsecond
FROM `users` AS a, `users` AS b
WHERE a.name = b.name
AND a.id <> b.id
AND a.id < b.id)

hope it work

Edit Answer

jaman posted

select + delete in single query , how to ?

Mitul Dabhi answered Nov 30 '-1 00:00

Related Questions

Insert link

Insert image

Mitul Dabhi
answered Nov 30 '-1 00:00