如何在不级联的情况下从多个表中删除?(SQLSERVER T-SQL)

Question

如何在不级联的情况下从多个表中删除？

我想从表中删除tbInstTypeTestAttribData、tbTestAttribData、tbInstTypeResultAttribData、tbTestResult和tbTest中有关pki4Test和pki4Transfer匹配的记录，并从tbDnTestData和tbDnReagentData中作为 FK 进行匹配。

例如，我想使用加入等并使用tbDnTestData.pki4Test = tbInstTypeTestAttribData.pki4Test AND tbDnTestData.pki4Transfer = tbInstTypeTestAttribData.pki4Transfer而不是使用tbInstTypeTestAttribData.pki4Test = 2226 AND pki4Transfer = 79684

DELETE FROM tbDnTestData where fki4ReagentLot = 201
DELETE FROM tbDnReagentData where pki4ReagentLot = 201
DELETE FROM tbInstTypeTestAttribData WHERE tbInstTypeTestAttribData.pki4Test =  2226 AND pki4Transfer = 79684
DELETE FROM tbTestAttribData WHERE tbTestAttribData.pki4Test = 2226 AND pki4Transfer = 79684
DELETE FROM tbInstTypeResultAttribData WHERE tbInstTypeResultAttribData.pki4Test = 2226 AND pki4Transfer = 79684
DELETE FROM tbTestResult WHERE tbTestResult.pki4Test = 2226 AND pki4Transfer = 79684
DELETE FROM [tbTest] WHERE [tbTest].pki4Test = 2226 AND pki4Transfer = 79684

Answer 1

假设pki4Test和pki4Transfer是子表中的外键，tbInstTypeTestAttribData、tbTestAttribData、tbInstTypeResultAttribData、tbTestResult、tbDnReagentData和tbDnTestData是父表。下面是如何在不级联的情况下从 SQL Server 中的这些表中删除记录的示例：

BEGIN TRANSACTION
DELETE FROM tbInstTypeTestAttribData 
WHERE pki4Test IN (SELECT pki4Test FROM tbDnTestData WHERE pki4Transfer = @pki4Transfer);
DELETE FROM tbTestAttribData
WHERE pki4Test IN (SELECT pki4Test FROM tbDnTestData WHERE pki4Transfer = @pki4Transfer);
DELETE FROM tbInstTypeResultAttribData
WHERE pki4Test IN (SELECT pki4Test FROM tbDnTestData WHERE pki4Transfer = @pki4Transfer);
DELETE FROM tbTestResult
WHERE pki4Test IN (SELECT pki4Test FROM tbDnTestData WHERE pki4Transfer = @pki4Transfer);
DELETE FROM tbDnReagentData
WHERE pki4Test IN (SELECT pki4Test FROM tbDnTestData WHERE pki4Transfer = @pki4Transfer);
DELETE FROM tbDnTestData
WHERE pki4Transfer = @pki4Transfer;
COMMIT TRANSACTION

在此示例中，我们将变量@pki4Transfer传递给用于基于外键筛选记录的查询。我们使用子查询从tbDnTestData表中获取pki4Test值的列表，其中pki4Transfer等于@pki4Transfer。最后，我们将从所有子表中逐个删除匹配的记录。请注意，BEGIN TRANSACTION和COMMIT TRANSACTION语句用于确保所有更改都作为单个事务进行。