How do I UPDATE from a SELECT in SQL Server?

How to UPDATE a Table from a SELECT Statement in SQL Server

Learn various techniques to update records in one SQL Server table based on data retrieved from another table or a subquery, enhancing your data manipulation skills.

Updating data in SQL Server often involves more than just setting static values. Frequently, you need to update columns in one table using values derived from another table or a complex query. This article explores several robust methods to achieve this, including using JOIN clauses, subqueries, and the MERGE statement, providing practical examples for each.

Understanding the Challenge

The core challenge is to correlate rows between the target table (the one being updated) and the source table (the one providing the new data). SQL Server offers several powerful constructs to handle this, each with its own advantages depending on the complexity and specific requirements of your update operation.

flowchart TD
    A[Start Update Process] --> B{Identify Target Table (T1)}
    B --> C{Identify Source Table (T2)}
    C --> D{Define Join Condition}
    D --> E{Specify Columns to Update}
    E --> F{Execute UPDATE Statement}
    F --> G[End Update Process]

General flow for updating a table from a SELECT statement.

Method 1: Using JOIN in the UPDATE Statement

This is often the most straightforward and readable method for updating a table based on another. You can join the table you want to update with the table containing the source data directly within the UPDATE statement. This approach is highly efficient for one-to-one or many-to-one relationships.

UPDATE T1
SET T1.ColumnToUpdate = T2.SourceColumn
FROM TargetTable AS T1
JOIN SourceTable AS T2
    ON T1.MatchingColumn = T2.MatchingColumn
WHERE T1.SomeCondition = 'Value';

Basic UPDATE statement with an INNER JOIN.

Method 2: Using Subqueries

Subqueries provide a flexible way to retrieve the data needed for the update. They can be used in the SET clause to assign a single value per row or in the WHERE clause to filter which rows get updated. This method is particularly useful when the source data requires aggregation or more complex filtering before being applied to the target table.

-- Subquery in SET clause
UPDATE TargetTable
SET ColumnToUpdate = (
    SELECT SourceColumn
    FROM SourceTable
    WHERE SourceTable.MatchingColumn = TargetTable.MatchingColumn
)
WHERE EXISTS (
    SELECT 1
    FROM SourceTable
    WHERE SourceTable.MatchingColumn = TargetTable.MatchingColumn
);

-- Subquery in WHERE clause (to update based on existence)
UPDATE TargetTable
SET ColumnToUpdate = 'NewValue'
WHERE EXISTS (
    SELECT 1
    FROM SourceTable
    WHERE SourceTable.MatchingColumn = TargetTable.MatchingColumn
    AND SourceTable.SomeOtherColumn = 'Criteria'
);

Examples of using subqueries in UPDATE statements.

Method 3: Using the MERGE Statement

The MERGE statement is a powerful T-SQL construct that allows you to perform INSERT, UPDATE, and DELETE operations on a target table based on the results of a JOIN with a source table. While often used for synchronizing tables, it can be simplified to perform an update-only operation, offering a clear, declarative syntax.

MERGE TargetTable AS T
USING SourceTable AS S
ON (T.MatchingColumn = S.MatchingColumn)
WHEN MATCHED THEN
    UPDATE SET T.ColumnToUpdate = S.SourceColumn
-- Optionally, add WHEN NOT MATCHED BY SOURCE or WHEN NOT MATCHED BY TARGET for other operations
OUTPUT $action, INSERTED.*, DELETED.*;

UPDATE operation using the MERGE statement.

Performance Considerations

The choice of method can impact performance. Generally, JOIN-based UPDATE statements are highly optimized by the SQL Server query optimizer and are often the most performant for simple correlations. Subqueries can sometimes lead to less optimal execution plans if not carefully constructed, especially correlated subqueries. MERGE statements are efficient for complex synchronization tasks but might introduce overhead if only a simple update is needed. Always analyze the execution plan for complex updates to ensure optimal performance.