How do I UPDATE from a SELECT in SQL Server?

To update a table in SQL Server using data from a SELECT query, you can combine the UPDATE statement with a join or a subquery. Here’s how to do it efficiently:

Basic Syntax

UPDATE target_table
SET target_column = source_column
FROM target_table
INNER JOIN source_table 
  ON target_table.matching_key = source_table.matching_key
WHERE [optional_conditions];

Example Scenarios

1. Update Using Another Table

Update the Products table with prices from a PriceUpdates table:

UPDATE p
SET p.Price = pu.NewPrice
FROM Products p
INNER JOIN PriceUpdates pu ON p.ProductID = pu.ProductID
WHERE pu.EffectiveDate = '2023-10-01';

2. Update Using a Subquery

Update employee salaries based on a subquery calculating department averages:

UPDATE e
SET e.Salary = dept.AvgSalary
FROM Employees e
INNER JOIN (
  SELECT DepartmentID, AVG(Salary) AS AvgSalary
  FROM Employees
  GROUP BY DepartmentID
) dept ON e.DepartmentID = dept.DepartmentID;

3. Update with a Common Table Expression (CTE)

WITH RankedSales AS (
  SELECT 
    SalespersonID, 
    TotalSales,
    RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
  FROM Sales
)
UPDATE s
SET s.Bonus = CASE 
                WHEN r.SalesRank = 1 THEN 1000 
                ELSE 500 
              END
FROM Sales s
INNER JOIN RankedSales r ON s.SalespersonID = r.SalespersonID;

Key Notes

Aliases: Use aliases (e.g., p for Products) to avoid ambiguity in column names.
Filter Rows: Add a WHERE clause to restrict updates to specific rows.
Test First: Run a SELECT with the same FROM/JOIN logic to verify the data before updating:

   SELECT p.Price AS OldPrice, pu.NewPrice
   FROM Products p
   INNER JOIN PriceUpdates pu ON p.ProductID = pu.ProductID;

Performance: For large datasets, ensure proper indexing on join keys.

Alternative: `MERGE` Statement

The MERGE statement (SQL Server 2008+) can handle updates, inserts, and deletes in one operation:

MERGE INTO Products AS target
USING PriceUpdates AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN
  UPDATE SET target.Price = source.NewPrice;

Common Pitfalls

Ambiguous Columns: Qualify column names with aliases (e.g., p.Price instead of Price).
Unintended Updates: Omit the WHERE clause at your peril—it might update all rows!
Transactional Safety: Wrap in a transaction for critical operations:

  BEGIN TRANSACTION;
  -- Run update here
  SELECT * FROM Products WHERE ProductID IN (...); -- Verify
  COMMIT TRANSACTION; -- or ROLLBACK

Result

You’ve updated the target table using data from a SELECT query, leveraging SQL Server’s UPDATE + FROM/JOIN syntax. Always validate with a SELECT first!