How to Correctly Delete Rows Using INNER JOIN in SQL Server?

The correct way to use INNER JOIN to delete rows in SQL Server

When deleting rows using INNER JOIN in SQL Server, be sure to avoid common mistakes that can cause the operation to fail. One such mistake is to omit explicit specification of the table to be dropped.

Error encountered:

When running the following code, you encounter the error message:

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

The error message is as follows:

Msg 156, Level 15, State 1, Line 15
Syntax error near keyword 'INNER'.

Solution:

To correct this error, you need to explicitly identify the table that will be modified by the DELETE operation. This can be achieved by introducing aliases to the table. The modified code is as follows:

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

In this updated code, "w" is used as an alias for the WorkRecord2 table. This ensures that the DELETE statement targets the correct table to delete rows.

The above is the detailed content of How to Correctly Delete Rows Using INNER JOIN in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!