Delete Using INNER JOIN

The need to join tables in order to determine which records to delete is a common requirement. The syntax can be somewhat tricky because you need to use an alias for the table you want to delete from.

Always test DELETE statements prior to running them in your production system.

First, here is a simple example:

[cc lang=”sql”]
DELETE o
FROM #orders o
JOIN #customers c
ON c.customer_id = o.customer_id
WHERE c.last_name = ‘jones’
AND c.first_name = ‘alexandria’
[/cc]

The following query will delete all the order records from the customers who have the name alexandria jones. This will only delete records from the orders table, however I strongly suggest you perform a SELECT statement prior to actually performing the delete using the same query as such:

[cc lang=”sql”]
–DELETE o
SELECT o.*
FROM #orders o
JOIN #customers c
ON c.customer_id = o.customer_id
WHERE c.last_name = ‘jones’
AND c.first_name = ‘alexandria’
[/cc]

This will show you the exact records you are about to delete. Once you get the count, I actually suggest you take one additional safety measure to ensure you don’t delete more records by using the TOP clause:

[cc lang=”sql”]
–DELETE o
SELECT count(o.*)
FROM #orders o
JOIN #customers c
ON c.customer_id = o.customer_id
WHERE c.last_name = ‘jones’
AND c.first_name = ‘alexandria’

— if the above query returns 5 records, set 5 below
DELETE TOP (5) o
FROM #orders o
JOIN #customers c
ON c.customer_id = o.customer_id
WHERE c.last_name = ‘jones’
AND c.first_name = ‘alexandria’
[/cc]

Once again.. test, test, test before you delete!

3 comments
Shailkumar 23 Jun 2017 at 6:40 am

using with (nolock) in sql server

Pat jennings 16 Jun 2015 at 6:38 pm

When I try this command to delete I get the message ORA-00933 Comand not properly ended if I run it as a select it work fine.

mayur naik 01 Aug 2013 at 6:47 am

how to use join to delete a row from 2 tables

Featured Articles

 Site Author

  • Thanks for visiting!