How to Delete using INNER JOIN with SQL Server?

You need to specify what table you are deleting from, here is a version with an alias:

FROM WorkRecord2 w
INNER JOIN Employee e
  ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'

SQL Server does not support deleting data from multiple tables at once

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

begin transaction;

   declare @deletedIds table ( id int );

   delete t1
   output into @deletedIds
   from table1 t1
    join table2 t2
      on =
    join table3 t3
      on =;

   delete t2
   from table2 t2
    join @deletedIds d
      on =;

   delete t3
   from table3 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.

Similar Posts: