Delete Using INNER JOIN

Posted on August 29, 2012 by Derek Dieter
3
1

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!