Deleting Data from Another Table

The previous lesson covered the UPDATE ... FROM pattern for changing rows based on values in another table. This lesson covers the last of the three cross-table DML statements, DELETE, using the same join-based technique to decide which rows in one table should be removed based on matching rows, or the lack of them, in another.

Like UPDATE, SQL Server extends DELETE with a FROM clause that lets you join the table you are deleting from to another table and base the deletion on that match:

DELETE target_alias
FROM target_table AS target_alias
JOIN source_table AS source_alias
  ON target_alias.key_column = source_alias.key_column
WHERE search_condition;

The same alias rule from the UPDATE lesson applies here: once you alias the table in the FROM clause, that alias, not the original table name, goes right after DELETE.

Fred Fredericks has decided to resign from Widget Sales Service (WSS). Company policy is that former employees stay in the Employee table for historical record-keeping, but their rows in SalaryHistory get removed.

Fred Fredericks is EmployeeID 105. Since the row to delete lives in SalaryHistory, but the condition ("this is Fred Fredericks") is only identifiable through Employee, the statement needs to join the two tables:

DELETE S
FROM SalaryHistory AS S
JOIN Employee AS E
  ON S.EmployeeID = E.EmployeeID
WHERE E.LastName = 'Fredericks'
  AND E.FirstName = 'Fred';

S is the alias used right after DELETE, per the rule above. After this runs, SalaryHistory has two rows left, Duck's and Jones's, and Fred Fredericks's row 105 is gone from SalaryHistory while his row in Employee is untouched.
The same result can be written as a WHERE EXISTS subquery instead of a join, which is worth knowing since it is the safer general-purpose pattern covered next:

DELETE FROM SalaryHistory
WHERE EXISTS (
    SELECT 1
    FROM Employee AS E
    WHERE E.EmployeeID = SalaryHistory.EmployeeID
      AND E.LastName = 'Fredericks'
      AND E.FirstName = 'Fred'
);

A common cross-table delete is cleaning up child rows whose parent no longer exists, for example, line items left behind after their parent order record was removed some other way. A LEFT JOIN combined with an IS NULL check on the parent's key finds exactly those orphans:

DELETE od
FROM OrderDetails AS od
LEFT JOIN Orders AS o
  ON od.OrderID = o.OrderID
WHERE o.OrderID IS NULL;

The LEFT JOIN keeps every OrderDetails row in the result; o.OrderID IS NULL then isolates the ones with no matching Orders row at all.

WHERE EXISTS and WHERE NOT EXISTS are the ANSI-standard way to filter a delete against another table, and for most queries SQL Server's optimizer produces an equivalent plan whether you write the condition as a join or as EXISTS, so there is little practical performance difference between the two. The same is not true of IN and NOT IN, and NOT IN in particular is worth actively avoiding for cross-table deletes, for two reasons:

• The NULL trap. If the subquery in a NOT IN list returns even one NULL, the entire NOT IN comparison evaluates to UNKNOWN for every row, and the DELETE silently removes nothing at all. NOT EXISTS has no such trap, since it tests for the presence of a matching row rather than comparing against a list.
• The unqualified-column trap. If a column referenced inside the subquery does not actually exist in the subquery's own source table, but a column of that name exists in the outer (target) table, SQL Server does not raise an error. It resolves the reference against the outer table instead, turning the subquery into an accidental correlated subquery that compares a row to itself. Depending on the condition, that can silently match, and delete, far more rows than intended. Qualifying every column in a subquery with its table alias is the simplest guard against this; using NOT EXISTS with explicitly aliased columns, as in the Fred Fredericks example above, avoids it by construction.

SQL Server allows a DELETE to run directly against a Common Table Expression (CTE), as long as the CTE reads from a single base table. This is a clean way to remove duplicate rows identified by ROW_NUMBER():

WITH DuplicateRows AS (
    SELECT
        StagingID,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerID, OrderDate
            ORDER BY ImportedAt DESC
        ) AS RowNum
    FROM StagingOrders
)
DELETE FROM DuplicateRows
WHERE RowNum > 1;

ROW_NUMBER() numbers each group of duplicate rows (same CustomerID and OrderDate) starting at 1 for the most recently imported row; deleting everywhere RowNum > 1 keeps exactly one row per group and removes the rest.

MERGE can delete rows as part of synchronizing a target table against a source, in two distinct ways: deleting matched rows that meet a condition, or deleting target rows that no longer exist in the source at all:

MERGE INTO ActiveCatalog AS tgt
USING MasterProducts AS src
   ON tgt.ProductID = src.ProductID
WHEN MATCHED AND src.IsDiscontinued = 1 THEN
    DELETE
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

The first WHEN clause removes catalog rows the source explicitly flags as discontinued; the second removes catalog rows whose product no longer appears in the source table at all, NOT MATCHED BY SOURCE meaning "present in the target, absent from the source."

Deleting a row does not have to mean losing its data. An OUTPUT clause reading from the deleted virtual table can capture exactly what was removed and write it to an archive table, in the same statement that deletes it. Returning to WSS: before Fred Fredericks's SalaryHistory row disappears, it can be preserved here:

DELETE S
OUTPUT
    deleted.EmployeeID,
    deleted.PriorSalary,
    deleted.RaiseDate,
    GETDATE() AS ArchivedAt
INTO SalaryHistoryArchive (EmployeeID, PriorSalary, RaiseDate, ArchivedAt)
FROM SalaryHistory AS S
JOIN Employee AS E
  ON S.EmployeeID = E.EmployeeID
WHERE E.LastName = 'Fredericks'
  AND E.FirstName = 'Fred';

This runs the delete and the archive write as one atomic operation, so there is no window where the row has been deleted but not yet recorded anywhere, and no separate SELECT needed beforehand to know what is about to go.

DELETE is destructive in a way SELECT is not, and a few habits make cross-table deletes safer to run:

• Run the WHERE/JOIN condition as a SELECT first to see exactly which rows match, before switching it to a DELETE.
• Wrap the statement in an explicit transaction so it can be rolled back if the row count looks wrong.
• Make sure there is a recent backup, or a restore point, before running a delete against production data.
• Check that foreign key constraints are configured the way you expect, so a delete does not unexpectedly cascade, or get blocked, somewhere you did not anticipate.

Ready to practice? The exercise below gives you another scenario for deleting rows from one table based on values in another, so you can work through the join, NOT EXISTS, and safety questions on your own.
Deleting Data - Exercise In the next lesson, you will learn how to accommodate different datatypes in your queries.