两个表-如果两个表中都未引用ID，如何删除行 [英] Two tables - how to delete rows if ID not referenced in both tables

问题描述

我有两个表:

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

两个表中的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.

所以-如果IMAGES中的item_id与我的主列表"表中的任何最新item_id值都不对应，我想删除第二个图像"表中的所有行.

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'?

我一直在尝试这样的SQL脚本和子查询:

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?

推荐答案

您应该使用子查询

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

更多示例和解释.

这篇关于两个表-如果两个表中都未引用ID，如何删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！