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

Deleting from Multiple Tables with INNER JOIN in SQL Server

Unlike MySQL, SQL Server doesn't directly support deleting from multiple tables using an INNER JOIN. However, you can achieve the same effect with the following workaround:

Step 1: Use the "Deleted" Pseudo Table

Create a temporary table called "@deletedIds" to store the IDs of deleted rows from the first table:

begin transaction;

declare @deletedIds table ( id int );

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 2: Delete from Subsequent Tables

Use the "@deletedIds" table to delete from subsequent tables:

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

delete from t3
from table3 as t3 ...

Step 3: Commit the Transaction

Commit the transaction to make the changes permanent:

commit transaction;

Note: You can output deleted.id from the second delete statement if necessary for joining with the third table.

Alternative Approach: Triggers

Consider using a trigger on table1 that automatically deletes from table2 and table3 when a row is deleted from table1. This ensures referential integrity and eliminates the need for manual deletion.

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