Using Subqueries with INSERT, UPDATE, and DELETE

The SELECT statement module covered using a subquery to filter or calculate values inside a SELECT. This lesson covers the same technique applied to the three Data Manipulation Language (DML) statements: using a subquery to filter, populate, or recalculate data inside an INSERT, UPDATE, or DELETE.

A subquery can appear:

1. After an IN keyword
2. After a NOT IN keyword
3. After an EXISTS keyword
4. After a NOT EXISTS keyword
5. After an ANY keyword
6. After a SOME keyword
7. After an ALL keyword
8. When comparing values
9. Anywhere an expression is allowed

A subquery is most commonly used in place of joining two tables together, and shown as a subset of DELETE syntax, the general pattern looks like this:

DELETE FROM table
[FROM table [, ...]]
WHERE column [NOT] IN | [NOT] EXISTS | ANY | SOME | ALL | comparison (subquery)

The important thing to remember is that a subquery returns one or more values, and which form you use (IN, EXISTS, a scalar comparison) depends on how many rows and columns it returns. Testing a subquery on its own first, before wrapping it in the outer statement, is the easiest way to confirm what it actually returns before trusting it inside a larger INSERT, UPDATE, or DELETE.

As for performance, SQL Server 2025's optimizer, Intelligent Query Processing, active under the default compatibility level of 170, routinely rewrites a correlated EXISTS subquery into an efficient semi-join or anti-semi-join plan behind the scenes. In practice, there is little reason to prefer a hand-written join over a well-formed EXISTS subquery purely for performance; pick whichever form reads more clearly for the situation.

A subquery can appear anywhere an expression is allowed in an INSERT, UPDATE, or DELETE statement, with one exception: it cannot appear in an ORDER BY clause.

The standard pattern for bulk-loading rows from one or more source tables into a target table does not wrap the SELECT in parentheses:

INSERT INTO Sales.OrdersArchive (OrderID, CustomerID, OrderDate, TotalAmount)
SELECT
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    o.TotalAmount
FROM Sales.Orders AS o
WHERE o.Status = 'Completed'
  AND o.OrderDate >= '2025-01-01'
  AND o.OrderDate < '2026-01-01';

When inserting a single row, a scalar subquery (one that returns at most one value) can sit inside the VALUES list to look up a foreign key or other value dynamically:

INSERT INTO Production.Products (ProductName, CategoryID, Price, CreatedDate)
VALUES (
    'Quantum SSD 4TB',
    (SELECT CategoryID FROM Production.Categories WHERE CategoryName = 'Storage'),
    349.99,
    SYSDATETIME()
);

A NOT EXISTS subquery inside an INSERT is a common guard against inserting a row that is already there. Before giving Mickey Mouse a raise record, WSS can check whether that exact raise has already been logged:

INSERT INTO SalaryHistory (LastName, FirstName, PriorSalary)
SELECT LastName, FirstName, CurrentSalary
FROM Employee
WHERE LastName = 'Mouse'
  AND FirstName = 'Mickey'
  AND NOT EXISTS (
      SELECT *
      FROM SalaryHistory
      WHERE LastName = 'Mouse'
        AND FirstName = 'Mickey'
        AND PriorSalary = 20000
  );

The subquery runs once per candidate row from Employee. If a SalaryHistory row already exists for Mickey Mouse at a PriorSalary of 20000, NOT EXISTS evaluates false and the insert is skipped, so running this statement twice by accident does not create a duplicate record.

A scalar subquery, one that is guaranteed to return exactly one value, can sit directly in the SET clause. Here, WSS wants to give Mickey Mouse a raise equal to 150% of the current highest salary at the company:

UPDATE Employee
SET CurrentSalary = (
    SELECT MAX(CurrentSalary) * 1.5
    FROM Employee
)
WHERE LastName = 'Mouse'
  AND FirstName = 'Mickey';

The inner SELECT returns a single value, the highest CurrentSalary in the table times 1.5, and that value is applied only to the row matching Mickey Mouse.

A correlated subquery references a column from the outer table being updated, so SQL Server evaluates it once per qualifying row rather than once overall:

UPDATE c
SET c.TotalSpend = (
    SELECT COALESCE(SUM(i.InvoiceTotal), 0.00)
    FROM Sales.Invoices AS i
    WHERE i.CustomerID = c.CustomerID
)
FROM Sales.Customers AS c
WHERE c.IsActive = 1;

Watch out for NULLs here: if a correlated subquery in a SET clause finds no matching rows, it returns NULL. Wrapping the aggregate in COALESCE() or ISNULL(), as above, is what keeps a customer with no invoices at 0.00 instead of NULL.

When a condition in another table should decide which rows get updated, but nothing from that table needs to be pulled into the SET clause, EXISTS (or NOT EXISTS) in the WHERE clause is the right tool:

UPDATE p
SET p.UnitPrice = p.UnitPrice * 0.90
FROM Production.Products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM Sales.OrderDetails AS od
    JOIN Sales.Orders AS o ON od.OrderID = o.OrderID
    WHERE od.ProductID = p.ProductID
      AND o.OrderDate >= DATEADD(DAY, -90, SYSDATETIME())
);

Writing a separate scalar subquery for each column in the SET clause means recalculating the same thing more than once. A derived table in the FROM clause computes it once and updates every column from it in a single pass:

UPDATE c
SET
    c.OrderCount = summary.TotalOrders,
    c.LastOrderDate = summary.MaxDate
FROM Sales.Customers AS c
JOIN (
    SELECT
        CustomerID,
        COUNT(OrderID) AS TotalOrders,
        MAX(OrderDate) AS MaxDate
    FROM Sales.Orders
    GROUP BY CustomerID
) AS summary ON c.CustomerID = summary.CustomerID;

A subquery used with IN can return more than one value, which is useful when the condition does not uniquely identify a single row. WSS wants to remove every SalaryHistory record belonging to any employee named Mouse:

DELETE FROM SalaryHistory
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM Employee
    WHERE LastName = 'Mouse'
);

Because the subquery filters only on LastName, it can return more than one EmployeeID (Mickey's and Minnie's both), and every value it returns is honored by the IN list.

The lesson on deleting data from another table covered this in depth: if the subquery inside a NOT IN list returns even a single NULL, the entire expression evaluates to UNKNOWN and the DELETE removes nothing at all. EXISTS and NOT EXISTS test for the presence of a matching row instead of comparing against a list, so they do not carry that risk:

DELETE FROM Staging.CustomerImports
WHERE EXISTS (
    SELECT 1
    FROM Sales.Customers AS c
    WHERE c.EmailAddress = Staging.CustomerImports.EmailAddress
);

A few patterns are worth keeping in mind whenever a subquery drives an INSERT, UPDATE, or DELETE:

Ready to practice? The exercise below gives you another scenario for deleting data using a subquery, so you can work through the IN and EXISTS questions on your own.
Create Delete Statement using Subquery - Exercise
In the next lesson, you will learn about indexing strategies.