我正在尝试以下条件:如果 Loans 表中的任何贷款的 OutstandingAmount
DELETE A, B, C, D, E
FROM Loans AS T1
INNER JOIN Payments T2 ON T1.LoanID = T2.LoanID
INNER JOIN Repayments T3 ON T1.LoanID = T3.LoanID
INNER JOIN Histories T4 ON T1.LoanID = T4.LoanID
INNER JOIN LoanRequests T5 ON T1.RequestDate = T5.RequestDate AND T1.BID = T5.BID
INNER JOIN Commits T6 ON T1.RequestDate = T6.requestDate AND T1.BID = T6.BID
WHERE T1.OutstandingAmount < 0
然而,这个命令在“DELETE A, B”处给了我语法错误,我什至不确定这是否有效。
任何帮助将不胜感激。谢谢。
正如 Gordon Linoff 在他的评论中所写,您只能在每个删除语句中从一个表中删除。
你基本上有两个选择:
在外键中使用删除级联(这可能是最好的
要做的事)
对每个表使用一个删除语句,但将整个删除过程包装在一个事务中。
将删除级联添加到外键意味着删除并重新创建它们:
ALTER TABLE dbo.Loans
DROP CONSTRAINT FK_Loans_Payments;
ALTER TABLE dbo.Loans
ADD CONSTRAINT FK_Loans_Payments FOREIGN KEY (LoanID) REFERENCES Payments(LoanID) ON DELETE CASCADE;
使用事务来包装单个删除语句:
BEGIN TRASACTION
BEGIN TRY
DELETE c
FROM Commits
INNER JOIN Loans l ON l.RequestDate = c.RequestDate
WHERE l.OutstandingAmount < 0
DELETE lr
FROM LoanRequests lr
INNER JOIN Loans l ON l.RequestDate = lr.RequestDate
WHERE l.OutstandingAmount < 0
-- more of the same...
DELETE
FROM Loans
WHERE OutstandingAmount < 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)