Introduction

SQL Server supports UPDATE ... FROM ... JOIN syntax to update rows in one table based on values from another table. This is a T-SQL extension not available in standard SQL. The key is that the UPDATE target references the table alias from the FROM clause, and the JOIN provides the matching condition. Other databases (MySQL, PostgreSQL) have different syntax for the same operation.

Basic Syntax

1UPDATE a
2SET a.column1 = b.column1,
3    a.column2 = b.column2
4FROM TableA AS a
5INNER JOIN TableB AS b ON a.key = b.key
6WHERE some_condition;

The UPDATE a tells SQL Server which table to modify. The FROM clause with JOIN brings in the source data.

Example: Update Orders from Customers

1-- Update customer names in orders table from the customers table
2UPDATE o
3SET o.CustomerName = c.CustomerName,
4    o.CustomerEmail = c.Email
5FROM Orders AS o
6INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID
7WHERE o.OrderDate >= '2025-01-01';

This updates every order from 2025 onwards with the current customer name and email from the Customers table.

LEFT JOIN — Update with Optional Match

1-- Set discount to NULL for orders without a promo code match
2UPDATE o
3SET o.Discount = COALESCE(p.DiscountPercent, 0)
4FROM Orders AS o
5LEFT JOIN Promotions AS p ON o.PromoCode = p.Code;

With LEFT JOIN, rows in Orders without a matching promotion still get updated — p.DiscountPercent is NULL for non-matches, and COALESCE converts it to 0.

Multiple Joins

1UPDATE oi
2SET oi.UnitPrice = p.CurrentPrice,
3    oi.TaxRate = t.Rate
4FROM OrderItems AS oi
5INNER JOIN Products AS p ON oi.ProductID = p.ProductID
6INNER JOIN TaxRates AS t ON p.CategoryID = t.CategoryID
7WHERE oi.OrderID IN (SELECT OrderID FROM Orders WHERE Status = 'Pending');

You can join as many tables as needed. The WHERE clause filters which rows get updated.

UPDATE with Subquery (Alternative)

1-- Without JOIN — using a correlated subquery
2UPDATE Orders
3SET CustomerName = (
4    SELECT c.CustomerName
5    FROM Customers AS c
6    WHERE c.CustomerID = Orders.CustomerID
7)
8WHERE EXISTS (
9    SELECT 1 FROM Customers AS c
10    WHERE c.CustomerID = Orders.CustomerID
11);

This achieves the same result but is less readable for complex multi-table updates. The EXISTS check prevents setting CustomerName to NULL for orders without matching customers.

UPDATE with CTE

1WITH UpdatedPrices AS (
2    SELECT
3        oi.OrderItemID,
4        oi.UnitPrice AS OldPrice,
5        p.CurrentPrice AS NewPrice
6    FROM OrderItems AS oi
7    INNER JOIN Products AS p ON oi.ProductID = p.ProductID
8    WHERE oi.UnitPrice <> p.CurrentPrice
9)
10UPDATE UpdatedPrices
11SET OldPrice = NewPrice;

CTEs make complex update logic more readable. The CTE acts as a view that you update directly.

UPDATE with OUTPUT (Audit Trail)

1UPDATE o
2SET o.Status = 'Shipped'
3OUTPUT
4    deleted.OrderID,
5    deleted.Status AS OldStatus,
6    inserted.Status AS NewStatus,
7    GETDATE() AS UpdatedAt
8INTO @AuditLog
9FROM Orders AS o
10INNER JOIN Shipments AS s ON o.OrderID = s.OrderID
11WHERE s.ShippedDate IS NOT NULL
12  AND o.Status = 'Processing';

The OUTPUT clause captures before and after values for auditing.

MERGE Statement (Upsert)

For insert-or-update operations, use MERGE:

1MERGE INTO Inventory AS target
2USING WarehouseStock AS source
3ON target.ProductID = source.ProductID
4WHEN MATCHED THEN
5    UPDATE SET target.Quantity = source.Quantity,
6               target.LastUpdated = GETDATE()
7WHEN NOT MATCHED THEN
8    INSERT (ProductID, Quantity, LastUpdated)
9    VALUES (source.ProductID, source.Quantity, GETDATE());

Cross-Database Syntax Comparison

1-- SQL Server (T-SQL)
2UPDATE a SET a.col = b.col
3FROM TableA a JOIN TableB b ON a.id = b.id;
4
5-- MySQL
6UPDATE TableA a JOIN TableB b ON a.id = b.id
7SET a.col = b.col;
8
9-- PostgreSQL
10UPDATE TableA a SET col = b.col
11FROM TableB b WHERE a.id = b.id;
12
13-- Standard SQL (subquery)
14UPDATE TableA SET col = (
15    SELECT b.col FROM TableB b WHERE b.id = TableA.id
16) WHERE EXISTS (
17    SELECT 1 FROM TableB b WHERE b.id = TableA.id
18);

Each database has different syntax. SQL Server and MySQL put JOIN in different positions.

Performance Tips

1-- Add an index on the join column for large tables
2CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
3
4-- Update in batches for very large tables
5WHILE 1 = 1
6BEGIN
7    UPDATE TOP (10000) o
8    SET o.Status = 'Archived'
9    FROM Orders AS o
10    WHERE o.OrderDate < '2020-01-01'
11      AND o.Status <> 'Archived';
12
13    IF @@ROWCOUNT = 0 BREAK;
14END

Batch updates prevent long-running transactions that lock the table.

Common Pitfalls

• Ambiguous table in UPDATE: UPDATE Orders SET ... without a FROM clause updates based on the table directly. Adding FROM Orders JOIN ... creates a second reference to Orders. Use an alias: UPDATE o SET ... FROM Orders AS o JOIN ....
• Multiple matches: If the JOIN produces multiple matching rows in the source table, SQL Server picks one arbitrarily. The result is non-deterministic. Use ROW_NUMBER() or TOP 1 in a subquery to ensure one match per target row.
• Missing WHERE clause: An UPDATE ... JOIN without WHERE updates every matching row. Always verify with a SELECT using the same FROM/JOIN/WHERE before running the UPDATE.
• Deadlocks on large updates: Updating millions of rows in a single transaction acquires many locks and can deadlock with concurrent operations. Use batch updates with TOP (n) in a loop.
• Not testing with SELECT first: Replace UPDATE a SET ... with SELECT a.*, b.* using the same FROM/JOIN/WHERE to preview which rows will be affected before executing the update.

Summary

• Use UPDATE alias SET ... FROM table AS alias JOIN ... syntax in SQL Server
• The UPDATE target uses the alias defined in the FROM clause
• LEFT JOIN updates allow handling missing matches with COALESCE
• CTEs and OUTPUT clauses make complex updates readable and auditable
• Always preview with SELECT before running UPDATE to verify affected rows
• Batch large updates with TOP (n) in a loop to avoid lock escalation