Delete sql rows where IDs do not have a match from another table

by

Using LEFT JOIN/IS NULL:

DELETE b FROM BLOB b 
  LEFT JOIN FILES f ON f.id = b.fileid 
      WHERE f.id IS NULL

Using NOT EXISTS:

DELETE FROM BLOB 
 WHERE NOT EXISTS(SELECT NULL
                    FROM FILES f
                   WHERE f.id = fileid)

Using NOT IN:

DELETE FROM BLOB
 WHERE fileid NOT IN (SELECT f.id 
                        FROM FILES f)

Warning

Whenever possible, perform DELETEs within a transaction (assuming supported – IE: Not on MyISAM) so you can use rollback to revert changes in case of problems.

More Related Contents:

  1. Join vs. sub-query
  2. Find records from one table which don’t exist in another
  3. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  4. How to use GROUP BY to concatenate strings in MySQL?
  5. Natural Sort in MySQL
  6. MySQL get row position in ORDER BY
  7. Comparing date ranges
  8. Subqueries vs joins
  9. MySQL Results as comma separated list
  10. MySQL unknown column in ON clause
  11. MySQL not using indexes with WHERE IN clause?
  12. SQL: Find the max record per group [duplicate]
  13. Group by month and year in MySQL
  14. Can you access the auto increment value in MySQL within one statement?
  15. Maintaining order in MySQL “IN” query
  16. MySQL: What is a reverse version of LIKE?
  17. Select TOP X (or bottom) percent for numeric values in MySQL
  18. How to create a multi-tenant database with shared table structures?
  19. MySQL #1140 – Mixing of GROUP columns
  20. MySQL select where column is not empty
  21. MySQL: GROUP_CONCAT with LEFT JOIN
  22. Hamming distance on binary strings in SQL
  23. Is it possible to have a default parameter for a mysql stored procedure?
  24. Renaming foreign-key columns in MySQL
  25. How to convert from varbinary to char/varchar in mysql
  26. MySQL GROUP BY behavior
  27. How to get rid of “Error 1329: No data – zero rows fetched, selected, or processed”
  28. Update multiple rows with one query?
  29. I want to copy table contained from one database and insert onto another database table
  30. How do MySQL views work?

Leave a Comment