How to Correctly Delete Records Using an INNER JOIN in SQL Server?

Correcting SQL Syntax for Deleting Records with INNER JOIN

This article addresses a common syntax error when deleting records from a table using an INNER JOIN in SQL Server. The objective is to remove entries from the WorkRecord2 table based on a relationship with the Employee table. The original query contained a syntax error. The corrected query is shown below:

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

The key correction is the introduction of aliases "w" for WorkRecord2 and "e" for Employee. The DELETE w clause clearly specifies that the deletion applies to the WorkRecord2 table (aliased as "w"). The FROM clause then uses these aliases to distinguish between the tables in the join. The ON clause correctly specifies the join condition using the aliases (w.EmployeeRun = e.EmployeeNo). Finally, the WHERE clause filters the deletion to only include records where the Company is '1' and the Date is '2013-05-06' within the Employee table (using alias "e"). This ensures only the relevant records are deleted from WorkRecord2. The corrected query will now execute successfully.

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