In SQL Server, to delete records from a table based on a criterion related to another table in the database, you can set up a join between the two tables within the same SQL Delete statement.
This is very similar to the method for performing an update using a join to update records based on values from other tables, but in this case, since we’re deleting records instead of updating them, the SQL syntax changes slightly.
If we want to delete records using a join, we simply need to specify in the first line of the statement the table or tables where the deletion should be applied, and on the next line, starting with FROM, the join between the necessary tables, along with the condition specified using WHERE.
SQL Example of Delete with Inner Join
A simple example of a delete with a join between tables to apply the delete condition:
DELETE TableWithRecordsToDelete FROM TableWithRecordsToDelete TDel INNER JOIN TableForDeleteCriteria TFilter ON TDel.idField_fk = TFilter.idField_pk WHERE TFilter.criteria = 'Value to filter'
Delete with Outer Join to Remove Records Without References in Another Table
This option of using an inner join to delete records can also be used with all types of joins. For example, combining it with an outer join is very useful to delete records from a table whose identifier is not found in another table, i.e., records from one table that have no matching entry in another.
You simply perform a left join with the table containing the reference identifiers, and all resulting records with a null value for the joined table are those whose value is missing in the second table — that is, the ones we want to delete from the first table.
As always, with an SQL example for deleting records that do not exist in another table, it becomes clearer:
DELETE TableWithRecordsToDelete FROM TableWithRecordsToDelete TDel LEFT JOIN TableWithValidRecords TMaster ON TDel.idField_fk = TMaster.idField_pk WHERE TMaster.idField_pk IS NULL
Delete with Join Across Multiple Tables for Cascading Deletes
In many cases, you may want to delete records from the main table and also from the table used in the join to define the selection criteria. This works like a cascading delete across both tables.
To delete records from multiple tables at once, you simply include the tables to delete from in the first line of the statement.
For the previous SQL delete example, if we also wanted to delete the records from the ‘linked’ table in the inner join to perform a cascading delete, we would just need to include the second table in the DELETE line, like this:
DELETE TableWithRecordsToDelete, TableForDeleteCriteria FROM TableWithRecordsToDelete TDel INNER JOIN TableForDeleteCriteria TFilter ON TDel.idField_fk = TFilter.idField_pk WHERE TFilter.criteria = 'Value to filter'
Precautions Before Performing a Delete
A highly recommended best practice before deleting records — aside from the usual database backup measures — is to always review the records that will be deleted to avoid unpleasant surprises after they’re gone.
The solution is as simple as first creating a select statement, and after visualizing and validating the records to be deleted, converting the select to a delete and proceeding with the deletion. For extra safety, you can even create a temporary copy in another table of just the records you’re going to delete, in case you later realize that some (or all) shouldn’t have been deleted and need to restore them as quickly as possible.
With the previously mentioned syntax, it’s as easy as replacing the line ‘delete [table_name]’ with ‘select *’, and if you want maximum security and want to save those records, just add ‘into [backup_table_name]’ to the ‘select *’ line.
I also always play with comment lines to work on the same query and ensure there’s no difference between the select and the delete.
Let’s go through this step by step using the first example in this post, the simple delete with join:
1. Select the Records I Want to Delete
Replace the delete (commented out) with select and preview the data to delete:
-- DELETE TableWithRecordsToDelete SELECT * FROM TableWithRecordsToDelete TDel INNER JOIN TableForDeleteCriteria TFilter ON TDel.idField_fk = TFilter.idField_pk WHERE TFilter.criteria = 'Value to filter'
2. Save the Records to a Table
In case I need to recover something later or check what was deleted:
-- DELETE TableWithRecordsToDelete SELECT * INTO CopyOfRecordsToDelete_yyyymmdd FROM TableWithRecordsToDelete TDel INNER JOIN TableForDeleteCriteria TFilter ON TDel.idField_fk = TFilter.idField_pk WHERE TFilter.criteria = 'Value to filter'
3. Delete the Records
Now I have more assurance that I won’t mess things up ;)
DELETE TableWithRecordsToDelete -- SELECT * FROM TableWithRecordsToDelete TDel INNER JOIN TableForDeleteCriteria TFilter ON TDel.idField_fk = TFilter.idField_pk WHERE TFilter.criteria = 'Value to filter'
And If I Messed Up and Need to Recover the Deleted Records?
I hope this little method helps — it costs almost nothing to do and can prevent many problems. Rolling back after a deletion is always a delicate matter that can get quite complicated.
In our case, if we took the precaution of saving the records we’re about to delete into another table, recovering one or more deleted records will be as simple as performing an insert into this table with a select from the table where we copied the data to be deleted.
Something like this query, which might save you from asking your DBA to restore a backup:
INSERT INTO TableWithRecordsToDelete SELECT * FROM CopyOfRecordsToDelete_yyyymmdd WHERE ...
