How can I delete using INNER JOIN with SQL Server?
I want to delete using INNER JOIN
in SQL Server 2008.
But I get this error:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'INNER'.
My code:
DELETE
FROM WorkRecord2
INNER JOIN Employee
ON EmployeeRun=EmployeeNo
WHERE Company = '1'
AND Date = '2013-05-06'
Solution 1:
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'
Solution 2:
Just add the name of the table between DELETE
and FROM
from where you want to delete records, because we have to specify the table to delete. Also remove the ORDER BY
clause because there is nothing to order while deleting records.
So your final query should be like this:
DELETE WorkRecord2
FROM WorkRecord2
INNER JOIN Employee
ON EmployeeRun=EmployeeNo
WHERE Company = '1'
AND Date = '2013-05-06';