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

Deleting from Multiple Tables with INNER JOIN in SQL Server

Unlike MySQL, SQL Server does not support the syntax for deleting from multiple tables using INNER JOIN. However, there are alternative methods to achieve the same result.

Using the "deleted" Pseudo Table

Consider the following code example:

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;

This code utilizes the "deleted" pseudo table to store the deleted IDs from the first delete statement. These IDs are then used in subsequent delete statements to delete the corresponding rows from other tables.

Other Considerations

• You can also use "output deleted." in the second delete statement if needed for joining with a third table.
• Consider using triggers on the parent table (e.g., table1) to automatically delete rows from the child tables (e.g., table2 and table3) when appropriate. This approach provides a convenient and efficient way to maintain data integrity across multiple tables.

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