Tags
, ,
Asked 30+ days ago
20 Dec 2016
Views 185
jaman

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 ?
Mitul Dabhi

Mitul Dabhi
answered Jan 23 '18 20:16

its easy to merge delete and select query .


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


so

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
Share
Post Answer