SQL DELETE with JOIN another table for WHERE condition

by

Due to the locking implementation issues, MySQL does not allow referencing the affected table with DELETE or UPDATE.

You need to make a JOIN here instead:

DELETE  gc.*
FROM    guide_category AS gc 
LEFT JOIN
        guide AS g 
ON      g.id_guide = gc.id_guide
WHERE   g.title IS NULL

or just use a NOT IN:

DELETE  
FROM    guide_category AS gc 
WHERE   id_guide NOT IN
        (
        SELECT  id_guide
        FROM    guide
        )

More Related Contents:

  1. Deleting a row based on the max value
  2. MySQL Error 1093 – Can’t specify target table for update in FROM clause
  3. Remove duplicate rows in MySQL
  4. How to delete from multiple tables in MySQL?
  5. MySQL DELETE FROM with subquery as condition
  6. SQL query: Delete all records from the table except latest N?
  7. Deleting rows with MySQL LEFT JOIN
  8. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  9. How do I delete all the duplicate records in a MySQL table without temp tables
  10. SQL UPDATE with sub-query that references the same table in MySQL
  11. Select from same table as an Insert or Update
  12. delete rows from multiple tables
  13. Insert and set value with max()+1 problems
  14. INSERT INTO SELECT statement with more columns which is not in another table
  15. 1052: Column ‘id’ in field list is ambiguous
  16. Count the occurrences of DISTINCT values
  17. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  18. Cumulative sum over a set of rows in mysql
  19. Set NOW() as Default Value for datetime datatype?
  20. Add Auto-Increment ID to existing table?
  21. MySQL SELECT DISTINCT multiple columns
  22. SQL query of multiple values in one cell
  23. How to skip certain database tables with mysqldump?
  24. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  25. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  26. How to get a list of months between two dates in mysql
  27. How to format number with “.” as thousands separator and “,” as decimal separator?
  28. Using DISTINCT and COUNT together in a MySQL Query
  29. How to check if field is null or empty in MySQL?
  30. ORDER BY alphabet first then follow by number

Leave a Comment