# SQL Server DELETE with INNER JOIN Syntax and Examples

> Write DELETE queries with INNER JOIN in SQL Server by specifying the target table after DELETE, with examples for JOIN, WHERE, and subquery-based alternatives

**URL:** https://sentry.io/answers/how-can-i-delete-using-inner-join-with-sql-server/

---

## 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:

```sql
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`.

```sql
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:

```sql
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:

```sql
DELETE Item
FROM Item
WHERE PersonId = 2;
```

### Delete With JOIN

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

```sql
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:

```sql
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:

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

---

*Source: [sentry.io/answers/how-can-i-delete-using-inner-join-with-sql-server/](https://sentry.io/answers/how-can-i-delete-using-inner-join-with-sql-server/)*
