How to delete or remove rows from table using JOINs in SQL Server? Example Tutorial

Hello guys, there are often a scenario where you need to delete data from a table by using joins. For example, you want to delete all employes who are also manager in this case, you need to self join with the table to find all the employees who are manager and then you need to delete them. Similarly, if you need to delete all employees who are not assigned any department then you need to use left or right out join to find them and then delete it. Bottom line is that 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.

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.

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. 

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.