How to Correctly Use INNER JOIN in SQL Server DELETE Statements?

SQL Server DELETE Statements with INNER JOIN: A Comprehensive Guide

Using INNER JOIN within SQL Server DELETE statements can sometimes lead to syntax errors. The most common error, "Incorrect syntax near the keyword 'INNER'," arises from an ambiguity in specifying the target table for deletion.

The solution is to use table aliases. This explicitly identifies the table from which rows should be removed. Here's a corrected example:

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

In this revised query, "w" serves as an alias for the WorkRecord2 table. This clearly designates the table affected by the DELETE operation, preventing the syntax error and ensuring the statement executes correctly. The WHERE clause then filters the rows to be deleted based on conditions involving both tables.

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