sql - 从多个相关表中删除

Question

我对 SQL Server 还很陌生，还在学习一些交易技巧，而另一个教我的人一周没有工作，通常我会先运行他的语句，然后再运行运行它。

我要做的是删除此 select 语句中返回的所有内容:

SELECT * from LOCATIONS a Join CONTACTS b on a.location_ID = b.Location_ID 
join CONTACTS_SOURCES c on b.contact_ID = c.Contact_ID  where c.Source_ID = 10014918

我需要删除此语句从 CONTACTS 表和 LOCATIONS 表返回的内容。执行此操作时，哪条路线是最佳路线？

A路:

delete from LOCATIONS a
Join CONTACTS b
on a.location_ID = b.Location_ID
join CONTACTS_SOURCES c
on b.contact_ID = c.Contact_ID  where c.Source_ID = 10014918

或路线 B。

DELETE from LOCATIONS where (SELECT * from LOCATIONS a
Join CONTACTS b
on a.location_ID = b.Location_ID
join CONTACTS_SOURCES c
on b.contact_ID = c.Contact_ID  where c.Source_ID = 10014918)

DELETE FROM CONTACTS where (SELECT * from LOCATIONS a
Join CONTACTS b
on a.location_ID = b.Location_ID
join CONTACTS_SOURCES c
on b.contact_ID = c.Contact_ID  where c.Source_ID = 10014918)

我感觉 Route A. 不会从 LOCATIONS 和 CONTACTS 表中删除。

Answer 1

您的感觉是对的:DELETE 查询(实际上是任何 DML 查询)只能影响单个表。 Route B 的问题是第二个查询不起作用，因为第一个查询删除了所有可能成为连接候选的行。

怎么样:

SELECT l.Location_ID, c.Contact_ID
  INTO #x
  FROM dbo.LOCATIONS AS l
  INNER JOIN dbo.CONTACTS AS c
  ON l.location_ID = c.Location_ID
  INNER JOIN dbo.CONTACTS_SOURCES AS cs
  ON c.contact_ID = cs.Contact_ID 
  WHERE cs.Source_ID = 10014918;

DELETE dbo.CONTACTS_SOURCES WHERE Contact_ID IN (SELECT Contact_ID FROM #x);

DELETE dbo.CONTACTS WHERE Contact_ID IN (SELECT Contact_ID FROM #x);

DELETE dbo.LOCATIONS WHERE Location_ID IN (SELECT Location_ID FROM #x);