How can I delete using INNER JOIN with SQL Server?
How can I 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不支持一次删除多张表中的数据
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.