在同一个SQL语句中，是否有可能从多个表中删除数据？

21 浏览
0 Comments

在同一个SQL语句中，是否有可能从多个表中删除数据？

可以使用连接语句来删除符合条件的数据集，例如下面的示例：

DELETE J
FROM Users U
INNER JOIN LinkingTable J ON U.id = J.U_id
INNER JOIN Groups G ON J.G_id = G.id
WHERE G.Name = 'Whatever'
AND U.Name NOT IN ('Exclude list')

然而，我希望在连接条件的两侧都删除数据，即删除`LinkingTable`记录和依赖它的`User`记录。由于我的解决方案是Entity Framework的Code First，而且双向关系会导致多个级联路径，所以无法开启级联删除。

理想情况下，我希望可以像下面这样做：

DELETE J, U
FROM Users U
INNER JOIN LinkingTable J ON U.id = J.U_id
...

从语法上看，这是不可行的，但我想知道是否有类似的解决方案？

0

3 答案

0 Comments

删除多个表中的数据是在SQL语句中同一时间进行的，这个问题的出现是因为需要执行复杂的逻辑操作来解决。

解决方法是通过逻辑上打破双向外键的方式来实现。具体步骤如下：

1. 向"Users"表中插入不可见的虚拟行，可以使用"Id = -1"来表示虚拟值。

2. 在"LinkingTable"表中添加一个额外的列，用于指向"Users"表，命名为"U_ComesFrom"。

3. 使用"NOCHECK"关键字添加一个"FOREIGN KEY"，将"U_ComesFrom_U_id"与"Users"表的"Id"列进行关联。

4. 在"Users"表中添加一个列"MarkedForDeletion"，用于标记是否删除，类型为BIT，默认值为0。

5. 随后的SQL代码如下：

BEGIN TRANSACTION
    UPDATE J
    SET U_Comes_From_U_id = U_ID, U_id = -1 -- or some N/R value that you define in Users
    FROM Users U
    inner join LinkingTable J on U.id = J.U_id
    inner join Groups G on J.G_id = G.id
    WHERE G.Name = 'Whatever'
    and U.Name not in ('Exclude list')
    UPDATE U
    SET MarkedForDeletion = 1
    FROM Users
    inner join LinkingTable J on U.id = J.U_ComesFrom_U_id 
    WHERE U_id > 0
    DELETE FROM LinkingTable 
    WHERE U_ComesFrom_U_id > 0
    DELETE FROM Users
    WHERE MarkedForDeletion = 1
COMMIT

这种方法会对性能产生影响，因为每次事务至少需要进行4个DML操作来处理双向外键。

0
0 Comments

在SQL SERVER中，不支持在同一个SQL语句中从多个表中删除数据。然而，在MySQL中是可以实现的。

为了在两个表中同时删除数据，可以使用"deleted"伪表，示例如下：

begin transaction;

declare table ( samcol1 varchar(25) );

delete #temp1

output deleted.samcol1 into

from #temp1 t1

join #temp2 t2

on t2.samcol1 = t1.samcol1

delete #temp2

from #temp2 t2

join d

on d.samcol1 = t2.samcol1;

commit transaction;

可以点击以下链接来详细了解：链接

使用"deleted"表的详细用法可以参考：Using the inserted and deleted Tables

0
0 Comments

在同一个SQL语句中从多个表中删除数据是否可能？

不可能，你需要运行多个语句。

因为你需要从两个表中删除数据，可以考虑创建一个匹配id的临时表：

SELECT U.Id INTO #RecordsToDelete
FROM Users U
JOIN LinkingTable J ON U.Id = J.U_Id
...

然后从每个表中删除数据：

DELETE FROM Users 
WHERE Id IN (SELECT Id FROM #RecordsToDelete)
DELETE FROM LinkingTable
WHERE Id IN (SELECT Id FROM #RecordsToDelete)

我认为问题出在双向外键上，这种情况下分开的语句也不会起作用（鸡生蛋蛋生鸡）。

如果是这种情况，那么在删除运行之后删除约束并添加回来可能也可以起作用。

我不认为EF - Code First允许你在运行时禁用/启用约束，而且在高并发环境中这可能不是最优的解决方案。如果你总是要关闭约束，那么开启它的意义是什么呢？

0