Using INNER JOIN in DELETE

Question

I'm trying to the following condition: If any loan in Loans table has OutstandingAmount < 0, delete all the relevant information in the database. I have to do it in a single command and hence, I have tried using INNER JOIN:

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

However, this command gives me syntax error at "DELETE A, B," and I'm not even sure will this work. Any help would be greatly appreciated. Thank you.

In SQL Server, you can only delete from one table at a time. Perhaps you want cascading foreign key relationships instead. — Gordon Linoff
– Gordon Linoff, Commented Mar 28, 2017 at 11:49
What are the A, B, C, D and E? They are not tables... are they columns? — asemprini87
– asemprini87, Commented Mar 28, 2017 at 12:55
@Veljko89 huh, I never heard them called "magic tables" before. After looking it up, I understand what you're referring to, I've just never heard them called that. — alroc
– alroc, Commented Mar 28, 2017 at 13:13

Zohar Peled · Accepted Answer · 2017-03-28 12:02:27Z

4

As Gordon Linoff wrote in his comment, you can only delete from one table in each delete statement.

You basically have 2 options:

Use on delete cascade in your foreign keys (that's probably the best thing to do)
Use a delete statement for each table, but wrap the entire delete process in a transaction.

Adding On delete cascade to your foreign keys means drop and re-create them:

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;

Using a transaction to wrap individual delete statements:

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

edited Mar 28, 2017 at 12:02

answered Mar 28, 2017 at 11:53

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KY Leung Over a year ago

Thank you so much. I have adopted both your methods (ON DELETE CASCADE and TRANSACTION). With ON DELETE CASCADE, I have successfully reduced the DELETE statements to only 2 and insert them into TRANSACTION. It works !! Thank you once again.

Collectives™ on Stack Overflow

Using INNER JOIN in DELETE

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related