SQL:删除使用3次连接找到的记录

Question

我使用SQL Server 2014数据库，和SQL Server管理工作室创建和运行查询。

表是:

的人

| ID | personName |
+----+------------+
| 1  |    Hamish  |
| 2  |    Morag   |
| 3  |    Ewan    |

汽车

| ID | CarName |
+----+---------+
| 1  |  Humber |
| 2  |  Austen |
| 3  |  Morris |

产品

| ID | GadgetName |
+----+------------+
| 1  |  Cassette  |
| 2  |     CD     |
| 3  |   Radio    |

CarToPersonMap

| ID | CarID | PersonID |
+----+-------+----------+
| 1  |   1   |    1     |

CarToGadgetMap

| ID | CarID | GadgetID |
+----+-------+----------+
| 1  |   2   |    2     |

映射表有相应的外键。

我想删除Car存在但未使用的记录。所以在上面的例子中，我想用ID = 3删除Car。

我有一个使用3个JOINs的SELECT语句，如下所示。语句运行正常，并返回正确的行，即Car和ID = 3。

SELECT * 
FROM
(SELECT Cars.*
FROM Cars 
LEFT JOIN CarToGadgetMap ON Cars.ID = CarToGadgetMap.CarID
WHERE CarToGadgetMap.CarID IS NULL) t1
JOIN
(SELECT Cars.*
FROM Cars 
LEFT JOIN PersonToCarMap ON Cars.ID = PersonToCarMap.CarID
WHERE PersonToCarMap.CarID IS NULL) t2 ON t1.ID = t2.ID

当我使用下面的代码尝试DELETE时，它删除了所有3行Car:

DELETE Cars 
FROM
(SELECT Cars.*
FROM Cars 
LEFT JOIN CarToGadgetMap ON Cars.ID = CarToGadgetMap.CarID
WHERE CarToGadgetMap.CarID IS NULL) t1
JOIN
(SELECT Cars.*
FROM Cars 
LEFT JOIN PersonToCarMap ON Cars.ID = PersonToCarMap.CarID
WHERE PersonToCarMap.CarID IS NULL) t2 ON t1.ID = t2.ID

结果信息:

(3 row(s) affected)

和检查显示Cars表中的所有3行已被删除。

为什么所有的记录被删除，当SELECT语句只返回1行?

有谁能帮忙吗?Thanks in advance

Answer 1

只需使用以下代码即可。我通过创建一个数据库来测试它。数据在我的结束。

DELETE Cars
FROM Cars 
LEFT JOIN CarToPersonMap ON Cars.ID = CarToPersonMap.CarID
LEFT JOIN CarToGadgetMap ON Cars.ID = CarToGadgetMap.CarID
WHERE CarToPersonMap.CarID IS NULL and  CarToGadgetMap.CarID IS NULL 

Answer 2

当执行DELETE或UPDATE语句时，要修改的表的计算方法如下:

• 查看FROM子句(在DELETE中，这是第二个FROM)，用于以该名称别名的顶级表。
• 否则，即使使用别名
，也要查找对Cars的单个顶级引用。
• 否则将表Cars与整个FROM子句交叉连接

DELETE的问题是顶级作用域没有名为或别名为Cars的表，所以它实际上只是在整个Cars表上做一个大的交叉连接。

你可以这样做

DELETE Cars
WHERE NOT EXISTS (SELECT 1
FROM CarToPersonMap cpm ON Cars.ID = cpm.CarID)
AND NOT EXISTS (SELECT 1
FROM CarToGadgetMap cgm ON Cars.ID = cgm.CarID);

还可以显式指定第二个FROM子句

DELETE Cars
FROM Cars
WHERE ...

Answer 3

可以用WHERE子句代替FROM。

DELETE cars WHERE cars.id = 3 (Get your query select only the id from your logic, rather than selecting all the tables with cars.*)