Mauricio Junior
Update Select on SQL Server
In SQL Server, there is no single UPDATE SELECT...
In SQL Server, there is no single "UPDATE SELECT" command. Instead, you can update a table with values from a query using several methods. The most common and efficient approach is using the UPDATE...FROM syntax.
Using UPDATE...FROM (Recommended)
This is the standard T-SQL way to update one table based on data from another. It uses a JOIN to link the target table with the source data.
UPDATE TargetTable
SET TargetTable.ColumnName = SourceTable.NewValue
FROM TargetTable
INNER JOIN SourceTable ON TargetTable.ID = SourceTable.ID
WHERE TargetTable.Condition = 'Value';
-
Pros: Highly readable and generally offers the best performance for bulk updates.
-
Tip: Always use table aliases to avoid confusion when column names are identical in both tables.
Using a Subquery
You can use a subquery directly in the SET clause if you only need to update one or two columns.
UPDATE TargetTable
SET ColumnName = (
SELECT NewValue
FROM SourceTable
WHERE SourceTable.ID = TargetTable.ID
)
WHERE EXISTS (
SELECT 1
FROM SourceTable
WHERE SourceTable.ID = TargetTable.ID
);
-
Caution: The subquery must return exactly one value. If it returns multiple rows, the UPDATE will fail.
-
Important: Without the WHERE EXISTS clause, any rows in TargetTable that don't have a match in SourceTable will be updated to NULL.
Using the MERGE Statement
The MERGE statement is a powerful alternative that handles updates, inserts, and deletes in a single command. It is often used for "upsert" operations.
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN MATCHED THEN
UPDATE SET Target.ColumnName = Source.NewValue;
-
Pros: Clean syntax for complex matching logic.
-
Citations: For advanced use cases involving both updates and inserts, refer to the Microsoft Learn MERGE documentation.
Using Common Table Expressions (CTE)
A CTE allows you to define the SELECT query first and then update it directly. SQL Server propagates the changes from the CTE to the underlying table.
WITH UpdatedData AS (
SELECT T.ColumnName, S.NewValue
FROM TargetTable T
JOIN SourceTable S ON T.ID = S.ID
)
UPDATE UpdatedData
SET ColumnName = NewValue;
- Pros: Very easy to debug because you can run the SELECT portion of the CTE separately to verify the data before executing the update.
Comparison Table
Method | Best For | Performance
UPDATE JOIN | Most standard updates | High
Subquery | Single-column, simple logic | Moderate
MERGE | Syncing tables (Insert + Update) | High (if indexed)
CTE Complex | filtering/Readability | High
Related articles
How to call a function inside my SQL Server?
Database function function and function
How to create an SQL to do multiple inserts in one statement?
Practical examples
Short: SQL Update using begin try and begin transaction
Commit and Rollback command