How to UPDATE from a SELECT in SQL Server

Updating records in SQL Server using data retrieved from a SELECT statement can be efficiently achieved through the use of a common table expression (CTE) or a subquery. This technique allows you to join tables, filter results, and update specific columns based on the retrieved data, all within a single SQL statement.

Using a Common Table Expression (CTE)

Defining the CTE:
A CTE can be used to organize the SELECT statement and simplify the UPDATE operation:

WITH CTE AS (
    SELECT a.Column1, b.Column2
    FROM TableA a
    JOIN TableB b ON a.ID = b.ID
    WHERE b.SomeColumn = 'someValue'
)
UPDATE a
SET a.Column1 = CTE.Column2
FROM TableA a
JOIN CTE ON a.ID = CTE.ID;

• WITH CTE AS: Defines the CTE.
• UPDATE a SET a.Column1 = CTE.Column2: Specifies the update operation.
• JOIN CTE ON a.ID = CTE.ID: Joins the CTE with the target table.

Example:

WITH EmployeeCTE AS (
    SELECT e.Salary, d.AverageSalary
    FROM Employees e
    JOIN Departments d ON e.DepartmentID = d.DepartmentID
    WHERE e.Role = 'Developer'
)
UPDATE Employees
SET Salary = EmployeeCTE.AverageSalary
FROM Employees
JOIN EmployeeCTE ON Employees.EmployeeID = EmployeeCTE.EmployeeID;

• Updates employee salaries based on average department salary for developers.

Using a Subquery

Subquery in UPDATE:
A subquery can be directly embedded in the UPDATE statement:

UPDATE TableA
SET Column1 = (
    SELECT b.Column2
    FROM TableB b
    WHERE b.ID = TableA.ID
)
WHERE EXISTS (
    SELECT 1
    FROM TableB b
    WHERE b.ID = TableA.ID
    AND b.SomeColumn = 'someValue'
);

• SET Column1 = (SELECT b.Column2 FROM TableB b WHERE b.ID = TableA.ID): Subquery to fetch new values.
• WHERE EXISTS: Ensures the subquery only affects existing matches.

Example:

UPDATE Products
SET Price = (
    SELECT MAX(p.Price)
    FROM PriceList p
    WHERE p.ProductID = Products.ProductID
)
WHERE EXISTS (
    SELECT 1
    FROM PriceList p
    WHERE p.ProductID = Products.ProductID
    AND p.PriceDate > '2024-01-01'
);

• Updates product prices based on the latest price from the PriceList table after a specific date.

Using JOIN in UPDATE

JOIN Syntax:
Directly join tables in the UPDATE statement:

UPDATE a
SET a.Column1 = b.Column2
FROM TableA a
JOIN TableB b ON a.ID = b.ID
WHERE b.SomeColumn = 'someValue';

• FROM TableA a JOIN TableB b ON a.ID = b.ID: Joins the tables.
• SET a.Column1 = b.Column2: Updates the target column.

Example:

UPDATE Employees
SET Employees.Salary = SalaryAdjustments.NewSalary
FROM Employees
JOIN SalaryAdjustments ON Employees.EmployeeID = SalaryAdjustments.EmployeeID
WHERE SalaryAdjustments.EffectiveDate = '2024-01-01';

• Adjusts employee salaries based on new salary data effective from a specific date.

Using MERGE Statement

MERGE Syntax:
The MERGE statement can handle updates, inserts, and deletions based on a condition:

MERGE INTO TableA AS target
USING (SELECT * FROM TableB WHERE SomeCondition) AS source
ON (target.ID = source.ID)
WHEN MATCHED THEN
    UPDATE SET target.Column1 = source.Column2
WHEN NOT MATCHED THEN
    INSERT (Column1, Column2) VALUES (source.Column1, source.Column2);

• *MERGE INTO TableA AS target USING (SELECT FROM TableB WHERE SomeCondition) AS source**: Defines the merge operation.
• WHEN MATCHED THEN UPDATE SET target.Column1 = source.Column2: Specifies the update action.

Example:

MERGE INTO Inventory AS target
USING (SELECT ProductID, NewQuantity FROM InventoryUpdates) AS source
ON (target.ProductID = source.ProductID)
WHEN MATCHED THEN
    UPDATE SET target.Quantity = source.NewQuantity
WHEN NOT MATCHED THEN
    INSERT (ProductID, Quantity) VALUES (source.ProductID, source.NewQuantity);

• Merges inventory updates into the target table, updating or inserting as necessary.

Summary

Updating records from a SELECT statement in SQL Server can be performed efficiently using different methods, each suited for various scenarios. Utilizing a common table expression (CTE) offers a clear and structured approach, while subqueries and direct JOINs provide straightforward alternatives. The MERGE statement is versatile, handling updates and inserts simultaneously. These techniques ensure flexibility and precision in managing data updates, making it easy to maintain and manipulate large datasets in SQL Server.