How to Delete from Multiple Tables with INNER JOIN in SQL Server?

Deleting from Multiple Tables with INNER JOIN in SQL Server

In MySQL, deleting from multiple tables using INNER JOIN can be accomplished with the syntax:

DELETE t1,t2 
FROM table1 AS t1 
INNER JOIN table2 t2 ...
INNER JOIN table3 t3 ...

However, SQL Server does not support this syntax. Instead, you can utilize the "deleted" pseudo table to achieve a similar result:

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;

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

delete from t3
from table3 as t3 ...

commit transaction;

By utilizing the "deleted.id" pseudo-table, you can perform a cascading delete on related tables, ensuring data integrity.

Alternative Approaches:

• Triggers: You can create a trigger on table1 that automatically deletes rows from related tables (table2 and table3). This ensures consistency but requires additional configuration.
• Cascading Foreign Keys: Configure cascading foreign keys between the involved tables to automatically handle deletions.

Ultimately, the most appropriate approach depends on the specific requirements of your system.

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