Sentry Answers>SQL Server>

How can I delete using INNER JOIN with SQL Server?

How can I delete using INNER JOIN with SQL Server?

Richard C.

The ProblemJump To Solution

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:

Click to Copy
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.

Click to Copy
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:

Click to Copy
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:

Click to Copy
DELETE Item FROM Item WHERE PersonId = 2;

Delete With JOIN

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

Click to Copy
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:

Click to Copy
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:

Click to Copy
DELETE FROM Item WHERE PersonId IN (SELECT Id FROM Person WHERE Name = 'Sofia');
  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

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.

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