How can I delete using INNER JOIN with SQL Server?

Richard C.

The Problem

In Microsoft SQL Server, how do you delete rows from a table, filtered by those joined on another table?

You may have received a SQL syntax error like Incorrect syntax near the keyword 'JOIN'. when running a delete query like:

DELETE
FROM TableA
JOIN TableB
    ON TableA.Id = TableB.AId

The error in the query above arises because, although the indentation implies that you are deleting only from TableA, you are actually deleting from TableA JOIN TableB. That’s impossible — you can delete from only one table at a time in SQL.

Similarly, if you write an identical query, but with INNER JOIN syntax instead of JOIN, you’ll get the error Incorrect syntax near the keyword 'INNER'.

The Solution

In SQL you have to specify the table you are deleting from. Let’s look at a simple example using two tables with a foreign key relationship: Person and Item.

CREATE TABLE Person
(
    Id INT PRIMARY KEY,
    Name VARCHAR(255)
);

CREATE TABLE Item
(
    Id INT PRIMARY KEY,
    Name VARCHAR(255),
    PersonId INT,
    FOREIGN KEY (PersonId) REFERENCES Person(Id)
);

INSERT INTO Person(Id, Name)
VALUES
    (1, 'Amir'),
    (2, 'Sofia');

INSERT INTO Item(Id, Name, PersonId)
VALUES
    (1, 'Chair', 1),
    (2, 'Bag', 2);

Assume you want to delete all items that belong to Sofia. In other words, the bag.

Simple Delete

The simplest delete query in SQL works on one table:

DELETE
FROM Item
WHERE PersonId = 2;

You can also specify the table from which to delete rows after the DELETE keyword in order to avoid any ambiguity:

DELETE Item
FROM Item
WHERE PersonId = 2;

Delete With JOIN

This syntax will fix your delete-with-join problem too. Here’s an example:

DELETE Item
FROM
    Item
    JOIN Person
    ON Item.PersonId = Person.Id AND Person.Name = 'Sofia';

Though the effect is the same, it might be clearer to write your query with a WHERE clause:

DELETE Item
FROM
    Item
    JOIN Person
    ON Item.PersonId = Person.Id
WHERE
    Person.Name = 'Sofia';

Note that the former query with the AND will incorrectly delete all rows in Item on the website SQLFiddle.com but will work correctly on a real installation of SQL Server. The latter query with the WHERE will work correctly on both.

A Generic Solution

While the command above will work on SQL Server and MySQL if you want one query that will work on both those servers and PostgreSQL use:

DELETE FROM Item
WHERE PersonId IN
    (SELECT Id FROM Person WHERE Name = 'Sofia');