如何在 SQL Server 2012 中删除按日期筛选的联接表

Question

我正在尝试从多个相互引用的表中删除旧数据，但这些表在所有表中没有一个公共列。此数据库更像是自上而下的设置，其中一个表列将引用后续表，然后该后续表将引用该表下的另一个表。我能够在下面的代码中选择我想要的所有数据，但我无法让它删除我的选择。

delete from Batches 
where exists (select *
from Batches 
full outer join Rolls on Batches.id = Rolls.batch_id
full outer join RollSessions on Rolls.id = RollSessions.roll_id 
full outer join Results on RollSessions.id = Results.rollSession_id
full outer join Dimensions on Results.id = Dimensions.result_id 
full outer join DimensionTols on Results.id = DimensionTols.result_id
full outer join Defects on Results.id = Defects.result_id
Where Batches.created_at < '2019-01-09 16:43:51.513')'

Answer 1

我会制作几个 DELETE 句子，每个表一个，从底部的表格开始。像这样：

DELETE FROM Defects 
WHERE Defects.result_id IN
(SELECT Defects.result_id
FROM Batches 
Full Outer Join Rolls on Batches.id = Rolls.batch_id
Full Outer Join RollSessions on Rolls.id = RollSessions.roll_id 
Full Outer Join Results on RollSessions.id = Results.rollSession_id
Full Outer Join Dimensions on Results.id = Dimensions.result_id 
Full Outer Join DimensionTols on Results.id = DimensionTols.result_id
Full Outer Join Defects on Results.id = Defects.result_id
Where Batches.created_at < '2019-01-09 16:43:51.513')
DELETE FROM DimensionTols 
WHERE DimensionTols.result_id IN
(SELECT DimensionTols.result_id
FROM Batches 
Full Outer Join Rolls on Batches.id = Rolls.batch_id
Full Outer Join RollSessions on Rolls.id = RollSessions.roll_id 
Full Outer Join Results on RollSessions.id = Results.rollSession_id
Full Outer Join Dimensions on Results.id = Dimensions.result_id 
Full Outer Join DimensionTols on Results.id = DimensionTols.result_id        
Where Batches.created_at < '2019-01-09 16:43:51.513')
DELETE FROM Dimensions 
WHERE Dimensions.result_id IN
(SELECT Dimensions.result_id
FROM Batches 
Full Outer Join Rolls on Batches.id = Rolls.batch_id
Full Outer Join RollSessions on Rolls.id = RollSessions.roll_id 
Full Outer Join Results on RollSessions.id = Results.rollSession_id
Full Outer Join Dimensions on Results.id = Dimensions.result_id                         
Where Batches.created_at < '2019-01-09 16:43:51.513')
DELETE FROM Results 
WHERE Results.rollSession_id IN
(SELECT Results.rollSession_id
FROM Batches 
Full Outer Join Rolls on Batches.id = Rolls.batch_id
Full Outer Join RollSessions on Rolls.id = RollSessions.roll_id 
Full Outer Join Results on RollSessions.id = Results.rollSession_id                         
Where Batches.created_at < '2019-01-09 16:43:51.513')

您继续这样做，直到到达最后一个 Delete 语句。它将是：

DELETE FROM Batches WHERE Batches.created_at < '2019-01-09 16:43:51.513'