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

SQL Server DELETE Statement with INNER JOIN: Avoiding Syntax Errors

This guide explains how to correctly use INNER JOIN within a DELETE statement in SQL Server, addressing a common syntax error. The error, "Incorrect syntax near the keyword 'INNER'," often occurs due to an omission in the DELETE statement's structure.

Understanding the Error and its Solution

The error message indicates that SQL Server cannot determine which table's rows should be deleted. The solution involves explicitly specifying the target table using a table alias.

Correct Syntax Example

The following corrected code demonstrates the proper usage:

<code class="language-sql">DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e ON e.EmployeeRun = w.EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06';</code>

Here, w acts as an alias for the WorkRecord2 table. This clearly designates WorkRecord2 as the table from which rows will be deleted based on the INNER JOIN conditions. The INNER JOIN itself links WorkRecord2 and Employee tables based on the specified join condition (e.EmployeeRun = w.EmployeeNo), filtering the deletion to only those rows meeting the WHERE clause criteria.

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