MySQL DELETE FROM with subquery as condition

by

For others that find this question looking to delete while using a subquery, I leave you this example for outsmarting MySQL (even if some people seem to think it cannot be done):

DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
             FROM tableE
             WHERE arg = 1 AND foo = 'bar');

will give you an error:

ERROR 1093 (HY000): You can't specify target table 'e' for update in FROM clause

However this query:

DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
             FROM (SELECT id
                   FROM tableE
                   WHERE arg = 1 AND foo = 'bar') x);

will work just fine:

Query OK, 1 row affected (3.91 sec)

Wrap your subquery up in an additional subquery (here named x) and MySQL will happily do what you ask.

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. What is better subquery vs literal on IN clause in mysql
  3. Getting the SUM() of claps and at the same time counting bookmarks in one query
  4. MySQL Error 1093 – Can’t specify target table for update in FROM clause
  5. How to delete from multiple tables in MySQL?
  6. SQL query: Delete all records from the table except latest N?
  7. Subqueries with EXISTS vs IN – MySQL
  8. MySQL/MariaDB – order by inside subquery
  9. SQL DELETE with JOIN another table for WHERE condition
  10. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  11. How do I delete all the duplicate records in a MySQL table without temp tables
  12. Deleting a row based on the max value
  13. i have an error which i dont understand. can you help me? [closed]
  14. In SQL Server how to get the count of child if both parent and child are available [closed]
  15. MySQL select 10 random rows from 600K rows fast
  16. Get a list of dates between two dates
  17. Calculate a running total in MySQL
  18. Simulate lag function in MySQL
  19. Get record counts for all tables in MySQL database
  20. Datetime equal or greater than today in MySQL
  21. How to get the next auto-increment id in mysql
  22. MySQL dynamic-pivot
  23. Can I reuse a calculated field in a SELECT query?
  24. Deleting duplicate rows from a table
  25. MySQL LIKE query with underscore
  26. Compare only day and month with date field in mysql
  27. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  28. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  29. delete rows from multiple tables
  30. MySQL Multiple Joins in one query?

Leave a Comment