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.

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!