How to delete from a table using JOIN in SQL Server

It's a little bit tricky to delete from a table while using any type of JOIN in SQL like Inner Join, Left Outer Join, or Right Outer Join. The obvious syntax doesn't work as shown below:

delete from #Expired e INNER JOIN 
Deals d ON e.DealId = d.DealId
Where d.Brand = 'Sony'

here I have a table with a list of expired deals that I want to delete from the Deals tables, but only for Sony.


When I run this SQL Query in Microsoft SQL Server database, it gave me the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'e'.

Now, I am puzzled, how to delete from a table while using INNER JOIN in SQL Server?

Well, it turned out, I wasn't using the right syntax. The DELETE clause needs to know the table from which data needs to be deleted.

By the way, If you are new to the SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.

You can solve the problem by using the table alias as shown below:

delete e from #Expired e INNER JOIN Deals d ON e.DealId = d.DealId
Where d.Brand = 'Sony'

This tells the SQL Server that deletes the matching row from the table with alias "e" i.e. the #Expired table. If you are not a fan of using table aliases then you can also give the full table name there as shown below, this will also work fine in the Microsoft SQL Server database.

delete #Expired from #Expired INNER JOIN Deals ON #Expired.DealId = Deals.DealId
Where Deals.Brand = 'Sony'

The same syntax will work fine even if you use the Left Outer Join or Right Outer Join.

SQL is like coding, both are key skills that require daily practice. If you don't write SQL queries or a couple of months, you will only remember the select * from table. More complex queries involving joining more than two tables in one query will be difficult to understand and write.

At the same time, you don't get the opportunity to write big, complex SQL queries on a daily basis, it's only when you need to generate reports, troubleshooting something, you need this skill.

This is where the book like SQL Puzzles and Answers from Joe Celko can help a lot. It's one of the best books for programmers who are serious about improving their SQL skills. Whenever I have some free time, I take on puzzles in this book. They are seriously worth doing, you will learn a thing or two with every puzzle.

Deleting From Table Using Join - SQL Example

Now, let's see a live example of how to delete records from a table using Join in SQL Server. In order to show something, we first need to create a Deal and Expired table. You can use the following SQL query to create those two tables.

I have purposefully created them as a temporary table, you can see the prefix # because they are for demonstration only. You should also not forget to drop them once you are done. A good SQL practice to follow.

-- creating a Deals table 
Create table #Deals (DealId BIGINT, Brand VARCHAR(100), ExpiryDate DATE);
Insert into #Deals values (10001, 'Sony', '20160102');
Insert into #Deals values (10002, 'Samsung', '20160103');
Insert into #Deals values (10003, 'HP', '20160104');
Insert into #Deals values (10004, 'Intel', '20160105');
Insert into #Deals values (10005, 'Citibank', '20160106');
Insert into #Deals values (10003, 'HSBC', '20160107');
Insert into #Deals values (10004, 'Disney', '20160108');
Insert into #Deals values (10005, 'Motorola', '20160109');

Select * from #Deals;

-- copying expired deals into a temporarily expired table
Select * into #Expired from #Deals where ExpiryDate < '20160106'
Select * From #Expired

-- cleanup
Drop table #Deals;
Drop table #Expired;

and here is the output of these SQL queries when you run them on Microsoft SQL Server 2014:

Now, let's use the inner join between both #Deals and #Expired to remove Expired entries for brand Sony from the #Expired table.

You can see that the first row with DealId=10001 has been deleted from the #Expired table because that's the row with the Sony brand.  If you feel SQL join is confusing and it's hard to understand what's going on, I suggest you join The Complete SQL Bootcamp course on Udemy. One of the best courses to learn SQL for beginners.

That's all about how to delete from a table when using Joins in SQL Server. I expect the query to work fine on other major relational databases like Oracle, MySQL, and PostgreSQL because I am not using anything SQL Server-specific, it's all ANSI SQL.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners