How to Delete Records from Multiple SQL Server Tables Using INNER JOIN?

Deleting Records from Multiple Tables with INNER JOIN in SQL Server

Unlike MySQL, SQL Server does not directly support the syntax used for deleting records from multiple tables using an INNER JOIN. However, a workaround can be achieved by leveraging the "deleted" pseudo table.

Step 1: Initialize a Temporary Table to Store Deleted IDs

begin transaction;

declare @deletedIds table ( id int );

Step 2: Delete from the First Table and Output Deleted IDs

delete from t1
output deleted.id into @deletedIds
from table1 as t1
inner join table2 as t2
  on t2.id = t1.id
inner join table3 as t3
  on t3.id = t2.id;

Step 3: Delete from the Second Table Using the Temporary Table

delete from t2
from table2 as t2
inner join @deletedIds as d
  on d.id = t2.id;

Step 4: Delete from Additional Tables (Optional)

delete from t3
from table3 as t3 ...

Step 5: Commit the Transaction

commit transaction;

Note: Outputting deleted IDs from the first delete allows you to continue joining for subsequent deletions.

Alternative Approach: Triggers

Alternatively, you can create triggers on the first table to handle the deletion of related records in other tables. This approach will ensure data integrity within an implicit transaction. However, it requires additional trigger maintenance and does not readily support complex join conditions.

The above is the detailed content of How to Delete Records from Multiple SQL Server Tables Using INNER JOIN?. For more information, please follow other related articles on the PHP Chinese website!