https://stackoverflow.com/questions/16481379/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:

DELETE w
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

https://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server

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

begin transaction;

   declare @deletedIds table ( id int );

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

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

   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.