当子表中没有parent_id关联时，删除父表中的行
当子表中没有parent_id关联时，删除父表中的行
提问于 2018-03-07 16:45:35
我有一个名为Location的父表，它有20,000行，还有两个子表，即Customer和Shop，它们大约有8000行。我想摆脱那些两张桌子上都没有孩子的地方。表格如下：
父表1:位置: ID_Location(PK)，ID_City(FK)
| Zip | ID_Location |
|------|-------------|
| 0001 | 1 |
| 0011 | 2 |
| 0002 | 3 |
| 0021 | 4 |
| 0003 | 5 |
| 0012 | 6 |表1:客户: Cust_ID(PK)，ID_Location(FK)
| Cust_ID | ID_Location |
|---------|-------------|
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 2 |
| 5 | 1 |儿童表3:商店: Shop_ID(PK)，ID_Location(FK)
| Shop_ID | ID_Location |
|---------|-------------|
| 1 | 1 |
| 2 | 2 |
| 3 | 6 |
| 4 | 3 |
| 5 | 1 | 所需的输出:我希望删除带有ID_Location =4的Location表中的行，因为它在两个子表中都没有任何子行。
我所做的就是，
select * from Location where ID_Location not in (select ID_Location from Customer)
select * from Location where ID_Location not in (select ID_Location from Shop)然后对出现的行执行delete查询，
delete from Location where ID_Location = 4是否有一种方法可以运行嵌套查询以获取子表中没有任何条目的行？
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-03-07 16:57:07
//One way of doing is getscalar cols from the child tables and exclude from
the parent
delete from Location
where ID_Location not in
(
Select ID_Location from Customer
union
Select ID_Location from Shop
)Stack Overflow用户
发布于 2018-03-07 16:52:30
这是你的查询
DELETE FROM LOC
FROM Location LOC
LEFT JOIN Customer CUST ON CUST.ID_Location = LOC.ID_Location
LEFT JOIN SHOP SH ON SH.ID_Location = LOC.ID_Location
WHERE CUST.ID IS NULL AND SH.ID IS NULL如果要确保在适当的记录中操作，请先运行以下命令：
SELECT LOC.*, CUST.ID, SH.ID
FROM Location LOC
LEFT JOIN Customer CUST ON CUST.ID_Location = LOC.ID_Location
LEFT JOIN SHOP SH ON SH.ID_Location = LOC.ID_Location
WHERE CUST.ID IS NULL AND SH.ID IS NULL页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49157137
复制相关文章
相似问题
腾讯云开发者
