时间戳

首页 > 解决方案 > 从具有两个父级的子表中删除，而无需在 sql server 中进行级联删除

sql - 从具有两个父级的子表中删除，而无需在 sql server 中进行级联删除

问题描述

我需要你的指导。我创建了一个脚本来分别从 3 个表中删除。意思是当我创建脚本时是从 table3（子）删除，然后从 2（子）删除，然后从 table1（父）删除我对 Table3 有问题，因为 table2 对 table2 和 table1 都有外键，所以我不能删除它在我删除 table2 之前，如果我先删除 table2，那么我会丢失用于查找要删除的行的引用，然后我从 table3 和 table1 中删除 0。

该脚本如下所示

DELETE FROM table3 t3
        INNER JOIN table1 t1 ON t3.t1ID = t1.t1ID
        INNER JOIN table2 t2 i ON t2.t1ID = t1.t1ID
        WHERE t3.t1ID IN(
         SELECT DISTINCT t1.t1ID            
                    FROM t2     
                        WHERE (
                              (t2.t2ID IS NULL AND t1.LastDate <@UpDate )))

上面删除 0，因为我在删除表 2 之前从

DELETE 
        FROM table2 t2 
         INNER JOIN table1 t1 on t2.T1ID =t1.t1ID 
         WHERE t2.t1ID IN(
         SELECT DISTINCT t1.T1ID            
                    FROM t2                 
                        WHERE (
                              (t2.t2ID is null and t1.LastDate <@UpDate <@UpDate )))

它也不会从 table1 中删除，因为脚本会查找 table2 中不存在的位置

DELETE 
        FROM table1 t1
        INNER JOIN table2 t2 i ON t2.t1ID = t1.t1ID
        WHERE t1.t1ID IN(
         SELECT DISTINCT t1.t1ID        
                    FROM t2 
                        WHERE (
                              (t2.t2 IS NULL ANDt1.LastDate <@UpDate)))

标签： sqlsql-serverparent-childsql-deletecascade

解决方案

尝试这样的事情：

DECLARE @RowsToDelete TABLE
(
    RowsToDeleteID int IDENTITY(1,1) PRIMARY KEY,
    Table1ID int,
    Table2ID int,
    Table3ID int
);

INSERT @RowsToDelete (Table1ID, Table2ID, Table3ID)
SELECT DISTINCT t1.t1ID, t2.t2ID, t3.t3ID 
FROM table3 AS t3
INNER JOIN table1 AS t1 
ON t3.t1ID = t1.t1ID
INNER JOIN table2 AS t2 
ON t2.t1ID = t1.t1ID
WHERE t2.t2ID IS NULL 
AND t1.LastDate < @UpDate;

DELETE table2
WHERE EXISTS (SELECT 1 FROM @RowsToDelete AS rtd WHERE rtd.Table2ID = t2ID);

DELETE table1
WHERE EXISTS (SELECT 1 FROM @RowsToDelete AS rtd WHERE rtd.Table1ID = t1ID);

DELETE table3
WHERE EXISTS (SELECT 1 FROM @RowsToDelete AS rtd WHERE rtd.Table3ID = t3ID);

没有桌子等来尝试，但看起来应该没问题。

我在这里还假设这些行在您执行此操作时可能不会发生变化，否则您可能会有竞争条件。

推荐阅读