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)
开发者_JAVA百科
But before I screw it all up, want to confirm if this is correct?
You should use a sub query
DELETE FROM images WHERE item_id NOT IN(SELECT item_id FROM listings)
More examples and explanation.
Here's a nice trick for dealing with these delicate situations:
Before you run a query which may cause significant harm if not written right, replace the DELETE/UPDATE with a SELECT to see which rows your query will affect. In your case, it would be:
SELECT *
-- DELETE
FROM images WHERE item_id NOT IN (SELECT item_id FROM listings)
Of course, you also want to cover yourself by backing up the db before issuing such a command. Even when your query looks correct with the SELECT test, you never know...
That would work, or the IN
clause as well.
DELETE
FROM image
WHERE item_id NOT IN (SELECT item_id FROM listings)
To me it'd be clearer to say
DELETE FROM images WHERE item_id NOT IN
(SELECT item_id FROM listings)
精彩评论