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 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');

Loved by over 4 million developers and more than 90,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

Share on Twitter
Bookmark this page
Ask a questionJoin the discussion

Related Answers

A better experience for your users. An easier life for your developers.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.