How to Delete Data From Multiple SQL Server Tables Using INNER JOIN?

Deleting From Multiple Tables with INNER JOIN in SQL Server

In SQL Server, you can employ the "deleted" pseudo table to achieve a similar effect as the MySQL syntax provided. The following steps outline how to delete from multiple tables using INNER JOIN:

Step 1: Open a Transaction

begin transaction;

Step 2: Create a Temporary Table to Store Deleted IDs

declare @deletedIds table ( id int );

Step 3: Delete from Table 1

This query deletes from Table 1 and outputs the deleted IDs into the @deletedIds table.

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 4: Delete from Table 2

This query deletes from Table 2 using the IDs stored in @deletedIds.

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

Step 5: Repeat for Remaining Tables

Repeat the deletion process for any additional tables that need to be modified.

Step 6: Commit the Transaction

commit transaction;

Additional Notes:

• You can use an "output deleted." statement in the second delete to enable joining on the third table if required.
• Consider implementing triggers on the first table to automate the deletion process from other tables.

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