开发者

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) 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜