How to delete from 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 e.g. 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 which I want to delete from the Deals tables, but only for Sony.

When I run this SQL Query in Microsoft SQL Server 2008, it gave me 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. You can solve the problem by using 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 full table name there as shown below, this will also work fine in 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 which require daily practice. If you don't write SQL queries or 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.

How to delete while using Left Join in SQL


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

How to delete from table using INNER Join in SQL



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

How to delete from table while using SQL

You can see that first row with DealId=10001 has been deleted from the #Expired table because that's the row with Sony brand.  If you feel SQL join is confusing and it's hard to understand what's going on, I suggest you to read Head First SQL. One of the better books in 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 e.g. Oracle, MySQL and PostgreSQL because I am not using anything SQL Server specific, it's all ANSI SQL.



No comments :

Post a Comment