How to delete from select in MySQL?

by

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.

More Related Contents:

  1. What is better subquery vs literal on IN clause in mysql
  2. Join vs. sub-query
  3. Select TOP X (or bottom) percent for numeric values in MySQL
  4. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  5. MySQL Insert query doesn’t work with WHERE clause
  6. Delete from two tables in one query
  7. Fulltext Search with InnoDB
  8. MySQL’s now() +1 day
  9. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  10. Rewriting mysql select to reduce time and writing tmp to disk
  11. Can you access the auto increment value in MySQL within one statement?
  12. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  13. What does DELIMITER // do in a Trigger?
  14. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  15. How do you version your database schema? [closed]
  16. How do I select all the columns from a table, plus additional columns like ROWNUM?
  17. MySQL, Get users rank
  18. Mysql: how to select groups having certain values?
  19. Best way to do nested case statement logic in SQL Server
  20. MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
  21. MySQL error 1241: Operand should contain 1 column(s)
  22. Boolean vs tinyint(1) for boolean values in MySQL
  23. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  24. Nested select statement in SQL Server
  25. Find rows that have the same value on a column in MySQL
  26. what is the difference between GROUP BY and ORDER BY in sql
  27. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  28. sql pulling a row for next or previous row of a current row
  29. Row Rank in a MySQL View
  30. Select Rows with Maximum Column Value group by Another Column

Leave a Comment