Search code examples
mysqlsqlsql-delete

Two tables - how to delete rows if ID not referenced in both tables

I have two tables:

listings(item_id, ...)
images(item_id,  ...)

The item_id value is the same in both tables - but I goofed and deleted listings from the 'listings' table without also deleting the corresponding row in the 'images' table.

So - I want to delete all rows in the second 'images' table if item_id in IMAGES doesn't correspond to any of the more up-to-date item_id values in my primary 'listings' table.

How do you delete all records in the 'images' table that are not referenced from 'listings'?

I've been experimenting with a SQL script and sub-query like this:

DELETE FROM images WHERE item_id IN
(SELECT item_id FROM images EXCEPT SELECT item_id FROM listings)

But before I screw it all up, want to confirm if this is correct?

Solution

  • You should use a sub query

    DELETE FROM images WHERE item_id NOT IN(SELECT item_id FROM listings)

    More examples and explanation.