How do I delete from multiple tables using INNER JOIN in SQL server

by

You can take advantage of the “deleted” pseudo table in this example. Something like:

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;

Obviously you can do an ‘output deleted.’ on the second delete as well, if you needed something to join on for the third table.

As a side note, you can also do inserted.* on an insert statement, and both inserted.* and deleted.* on an update statement.

EDIT:
Also, have you considered adding a trigger on table1 to delete from table2 + 3? You’ll be inside of an implicit transaction, and will also have the “inserted.” and “deleted.” pseudo-tables available.

More Related Contents:

  1. SQL where clause odd behavior [closed]
  2. How do I escape a single quote in SQL Server?
  3. Simple way to transpose columns and rows in SQL?
  4. Only inserting a row if it’s not already there
  5. Unpivot with column name
  6. Selecting data from two different servers in SQL Server
  7. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  8. SQL JOIN vs IN performance?
  9. Most efficient way in SQL Server to get date from date+time?
  10. Convert varchar into datetime in SQL Server
  11. TSQL Pivot without aggregate function
  12. How to generate a range of dates in SQL Server
  13. SQL RANK() versus ROW_NUMBER()
  14. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  15. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  16. How do I create a foreign key in SQL Server?
  17. How do I group on continuous ranges
  18. join comma delimited data column
  19. Need to list all triggers in SQL Server database with table name and table’s schema
  20. Count based on condition in SQL Server
  21. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  22. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  23. Best equivalent for IsInteger in SQL Server
  24. Store multiple bit values in a single table column
  25. Syntax of for-loop in SQL Server
  26. SQL – Call Stored Procedure for each record
  27. SQL Server SELECT INTO @variable?
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment