How to delete from select in MySQL?
217,535
Solution 1
SELECT
(sub)queries return result sets. So you need to use IN
, not =
in your WHERE
clause.
Additionally, as shown in this answer you cannot modify the same table from a subquery within the same query. However, you can either SELECT
then DELETE
in separate queries, or nest another subquery and alias the inner subquery result (looks rather hacky, though):
DELETE FROM posts WHERE id IN (
SELECT * FROM (
SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
) AS p
)
Or use joins as suggested by Mchl.
Solution 2
DELETE
p1
FROM posts AS p1
CROSS JOIN (
SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1
) AS p2
USING (id)
Solution 3
you can use inner join :
DELETE
ps
FROM
posts ps INNER JOIN
(SELECT
distinct id
FROM
posts
GROUP BY id
HAVING COUNT(id) > 1 ) dubids on dubids.id = ps.id
Author by
IAdapter
Updated on July 08, 2022Comments
-
IAdapter almost 2 years
This code doesn't work for MySQL 5.0, how to re-write it to make it work
DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 ))
I want to delete columns that dont have unique id. I will add that most of the time its only one id(I tried the in syntax and it doesnt work as well).
-
Alex almost 12 yearsI had a table with 150 duplicate keys. I executed the above query and it said "144 rows affected", but there where still duplicate keys. So I executed the query again and it says 5 rows affected, again: 1 row affected. Then all the duplicate keys where gone. Why is this?
-
havvg over 10 yearsThis is happening, because your are only deleting 1 entry out of each set of duplicates:
SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )
-
wintron about 10 yearsThis appears to work, but I'm confused by the syntax and can't find any resources elsewhere to explain it.
CROSS JOIN
apparently performs a cartesian join, so seems like this might do unnecessary work, or perform sub-optimally? Could anyone explain? -
Mchl about 10 yearsIt will do a cartesian product only if there's no
USING
clause. WithUSING
the product is limited to pairs having same value inid
column, so it is in fact very limited. -
thang almost 9 years#1248 - Every derived table must have its own alias
-
BoltClock almost 9 years@thang: Which is why I said to alias the inner subquery.
-
thang almost 9 yearsNo, still doesn't work. If you don't alias the inner subquery, it gives you a different error message. I ended up creating a temporary table, dumping everything to it, and then delete where id in that temp table.
-
BoltClock almost 9 years@thang: Post a separate question. As it is there is nothing I can do if all you're going to give me is an error message and "doesn't work".
-
BoltClock almost 9 years@thang: I tried it four years ago and just tried it again now, and it works.
-
thang almost 9 yearsI did the exact same thing. Did you use innodb? I wonder if it depends on the engine.
-
BoltClock almost 9 years@thang: I tested it on both MyISAM and InnoDB and it works. What is your version of MySQL? I highly recommend posting a separate question so you can provide more details.
-
thang almost 9 yearslet me dig into it some more. probably something stupid i am doing. if it still doesn't work, i'll post a new question. thanks!
-
Cricketer over 8 yearsWill you please explain what does the "As p" does?
-
Cave Johnson about 8 yearsCould you do the same thing with inner join? I.E.
DELETE p1 FROM posts AS p1 INNER JOIN ( SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1 ) AS p2 ON p2.ID=p1.ID
-
Mchl about 8 years@Andrew: Yes. Functionally these joins are exactly the same.
-
daisura99 almost 7 years@Samarth it is an alias for the derived table (SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 ))
-
Jivan Pal about 2 years@wintron
CROSS JOIN
is the same as[INNER] JOIN
in MySQL; it doesn't implementCROSS JOIN
in the way that ANSI SQL describes it.SELECT ... FROM a JOIN b USING (x)
is syntactic sugar forSELECT ... FROM a JOIN b ON b.x = a.x
.