如何在SQL服务器中使用INNER JOIN从多个表中删除数据？

Question

在MySQL中，您可以使用以下语法

DELETE t1,t2 
FROM table1 AS t1 
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

我要如何在SQL Server中实现同样的事情？

- Byron Whitlock

Answer 1

你可以利用此示例中的“deleted”伪表。类似于：

您可以利用此示例中的“删除”伪表。例如：

begin transaction;

   declare @deletedIds table ( id int );

   delete from t1
   output deleted.id into @deletedIds
   from table1 as t1
    inner join table2 as t2
      on t2.id = t1.id
    inner join table3 as t3
      on t3.id = t2.id;

   delete from t2
   from table2 as t2
    inner join @deletedIds as d
      on d.id = t2.id;

   delete from t3
   from table3 as t3 ...

commit transaction;

如果你需要一个东西来连接第三个表，那么在第二个delete语句中，显然也可以执行“output deleted.”。

另外，对于insert语句，你也可以使用inserted.*，而对于update语句，则可以使用both inserted.*和deleted.*。

编辑： 此外，你考虑过在table1上添加触发器以从table2 + 3中删除吗？你将处于隐式事务中，并且还将拥有“inserted.”和“deleted.”伪表。

- John Gibb

---

仅使用DELETE FROM table1 WHERE id = x，然后从下一个表中删除，而不是使用内部连接并通过所有这些额外的文本会更好吗？基本上，跳过内部连接，我只需要2个简单的查询... 或者这种方法是否更有效？ - Colandus

我认为这取决于你的where子句有多复杂。对于一个复杂的子句，这种方法更好，因为它只会发生一次。但是对于一个影响很多行的简单where子句，你的建议可能更有效，因为它不必在表变量中保存许多ID。 - John Gibb

@JohnGibb，这个答案是怎么工作的？你能解释一下这个答案，让一个MySQL开发者能够理解吗？ - Pacerier

@Pacerier 我对MySQL不是很熟悉。这个想法是第一个删除语句只从table1中删除，但它会将被删除的ID保存到一个变量中。随后的两个语句使用该变量从table2和table3中删除相关行。 - John Gibb

@JohnGibb，现在非常清楚了。您应该将其包含在答案中。 - Pacerier

Answer 2

您可以在SQL Server中的FROM子句中使用JOIN语法进行DELETE，但仍然只能从第一个表中删除，并且它是专有的Transact-SQL扩展，可作为子查询的替代方法。

例如，这里提供了一个例子：

 -- Transact-SQL extension
 DELETE 
   FROM Sales.SalesPersonQuotaHistory 
     FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN 
          Sales.SalesPerson AS sp ON spqh.BusinessEntityID = sp.BusinessEntityID
    WHERE sp.SalesYTD > 2500000.00;

- topchef

---

例子 D: 从销售.销售人员配额历史表中删除记录 从销售.销售人员配额历史表别名为spqh 内连接销售.销售人员表别名为sp ON spqh.BusinessEntityID = sp.BusinessEntityID WHERE sp.SalesYTD > 2500000.00; - Mark A

Answer 3

1. 您可以始终在表之间的关系上设置级联删除。

2. 您可以将多个删除封装在一个存储过程中。

3. 您可以使用事务来确保一次工作单元。

- Aaron Daniels

---

在联结语句中删除数据是完全可行的，我只是想一次从多个表中删除数据。 - Byron Whitlock

答案错误，连接语句可用于删除操作。 - rboarman

1.) 这并不是真的，有时候可能无法实现。在某些情况下，您无法设置级联删除，例如循环或多个级联路径。（例如，请参见https://dev59.com/WHRA5IYBdhLWcg3wuAYo#3548225） - Tom Pažourek

Answer 4

删除主表中的某些记录以及两个详细表中相应的记录的示例：

BEGIN TRAN

  -- create temporary table for deleted IDs
  CREATE TABLE #DeleteIds (
    Id INT NOT NULL PRIMARY KEY
  )

  -- save IDs of master table records (you want to delete) to temporary table    
  INSERT INTO #DeleteIds(Id)
  SELECT DISTINCT mt.MasterTableId
  FROM MasterTable mt 
  INNER JOIN ... 
  WHERE ...  

  -- delete from first detail table using join syntax
  DELETE d
  FROM DetailTable_1 D
  INNER JOIN #DeleteIds X
    ON D.MasterTableId = X.Id


  -- delete from second detail table using IN clause  
  DELETE FROM DetailTable_2
  WHERE MasterTableId IN (
    SELECT X.Id
    FROM #DeleteIds X
  )


  -- and finally delete from master table
  DELETE d
  FROM MasterTable D
  INNER JOIN #DeleteIds X
    ON D.MasterTableId = X.Id

  -- do not forget to drop the temp table
  DROP TABLE #DeleteIds

COMMIT

- Pavel Hodek

---

你能否使用 SELECT INTO #DeleteIds 替代 CREATE TABLE 'DeleteIds 后跟着 INSERT INTO 'DeleteIds...？ - Caltor

Answer 5

我在想...这在MySQL中真的可能吗？它会删除t1和t2吗？还是我误解了问题。

但如果你只想使用多个联接条件删除table1，那就不要给你想要删除的表设置别名。

例子:

DELETE t1,t2 
FROM table1 AS t1 
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

应该这样编写以在MSSQL中工作：

DELETE table1
FROM table1 
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

为了对比其他两种常见的关系型数据库管理系统如何执行删除操作：

http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html

- Michael Buen

---

谢谢你的SQL Server提示，我必须按照那些方向调整SQL。 - Pauk

Answer 6

基本上，您必须在事务中进行三个删除语句，先是子项，然后是父项。如果这不是一次性操作，并且其存在不会与任何现有触发器设置冲突，则设置级联删除是一个好主意。

- Yishai

---

我本来希望不必这样做，但既然关系不是父子关系，我想我得将ID选择到一个临时表中。一旦一张表的行消失了，就没有办法获取另一张表的行了。 - Byron Whitlock

Answer 7

在SQL Server中，没有办法使用联接从多个表中删除记录。因此，在删除父级之前必须先从子级删除。

- tiny

---

类似这样：DELETE ChildTable Where id=@id（换行）DELETE ParentTable Where id=@id？（ids是PK和FK） - paraJdox1

Answer 8

所有内容都已经指出。只需在父表上使用DELETE ON CASCADE或从child-table删除，然后再删除parent即可。

- kayode

---

你所说的从子表中删除到父表是什么意思？你是否使用像问题或前面的答案中展示的连接技术？ - Imran Faruqi

Answer 9

这是一种在不留下孤立记录的情况下删除记录的替代方法。

声明 @user 表（keyValue int，someString varchar(10)）
插入到 @user
值（1，'1 value'）
插入到 @user
值（2，'2 value'）
插入到 @user
值（3，'3 value'）
声明 @password 表（keyValue int，details varchar(10)）
插入到 @password
值（1，'1 Password'）
插入到 @password
值（2，'2 Password'）
插入到 @password
值（3，'3 Password'）
--删除前
  从 @password a 内连接 @user b
                on a.keyvalue = b.keyvalue
  选择 * into #deletedID from @user where keyvalue=1 -- 这与输出示例类似
  删除 @user where keyvalue =1
  删除 @password where keyvalue in (select keyvalue from #deletedid)
--删除后--
  从 @password a 内连接 @user b
                on a.keyvalue = b.keyvalue

- hidden

Answer 10

正如Aaron已经指出的那样，您可以将删除行为设置为CASCADE，这将在删除父记录时删除子记录。除非您想要发生某种其他魔法（在这种情况下，Aaron回复的第2、3点会很有用），否则我不明白为什么您需要使用内连接进行删除。

- Peter Perháč