How do I execute a DELETE using an INNER JOIN in SQL Server?

Data and Databases
1

Attempting an INNER JOIN DELETE in SQL Server 2008 results in a syntax error near the INNER keyword. Below is an example:

DELETE T
FROM TableX AS T
JOIN TableY AS Y ON T.colA = Y.colB
WHERE Y.companyID = '1';
2

hey, try sticking with a subquery method instead of a direct join. i found in older sql versions, rewriting the query to delete using a where condition on an in (select …) clause worked better in my tests. sometimes less fancy syntax avoids join ambiguities.

3

I have found that clarifying your DELETE statement by explicitly naming the target table helps avoid ambiguity. When constructing queries with INNER JOIN, SQL Server requires that you identify the deletion target right after DELETE. If the simple alias syntax generates an error, an alternative is to use a Common Table Expression to isolate the rows before deletion. This not only clarifies which table is being modified but also enhances maintainability when joining multiple tables. In practice, testing these approaches in your environment ensures they behave as expected across different SQL Server versions.