Updating Data from Another Table

The previous lesson covered constructing INSERT statements that pull values from another table. This lesson covers the UPDATE equivalent: changing rows in one table based on values or matching conditions found in another, using the same FROM clause technique.

SQL Server extends standard UPDATE with a FROM clause, letting you join the table being updated to a source table and pull its values into the SET list:

UPDATE target_alias
SET target_alias.column1 = source_alias.column1
FROM target_table AS target_alias
JOIN source_table AS source_alias
  ON target_alias.key_column = source_alias.key_column
WHERE search_condition;

One rule catches people off guard: once you alias the target table in the FROM clause, you must reference that alias, not the original table name, immediately after UPDATE. UPDATE target_table SET ... FROM target_table AS target_alias ... is a common mistake; UPDATE target_alias SET ... FROM target_table AS target_alias ... is correct.

In the previous lesson, Widget Sales Service (WSS) began tracking employee raises by inserting records from the Employee table into a SalaryHistory table. After reviewing several months of that history, WSS management has concluded employees are generally underpaid and wants to act: give a 10% raise to every employee who hasn't had a raise in the past 365 days, including employees with no raise on record at all.

Notice that Employee has more rows than SalaryHistory: Mickey and Minnie Mouse have never had a raise recorded at all. An INNER JOIN between the two tables would silently drop them from the update, which is the opposite of what WSS wants. A LEFT JOIN keeps every Employee row in the result, whether or not it has a matching SalaryHistory row:

UPDATE E
SET E.CurrentSalary = E.CurrentSalary * 1.1
FROM Employee AS E
LEFT JOIN SalaryHistory AS S
  ON E.LastName = S.LastName
 AND E.FirstName = S.FirstName
WHERE DATEDIFF(day, S.RaiseDate, GETDATE()) > 365
   OR S.RaiseDate IS NULL;

A few things worth calling out:

• E is the alias used right after UPDATE, matching the rule above.
• The join matches on LastName and FirstName together, since this Employee table has no separate ID column to join on.
• S.RaiseDate IS NULL is what catches Mickey and Minnie: for any Employee row with no matching SalaryHistory row, the LEFT JOIN fills every column from SalaryHistory, S.RaiseDate included, with NULL. Testing that directly is clearer than testing DATEDIFF(...) IS NULL, though both work, since DATEDIFF against a NULL date also returns NULL.
• DATEDIFF(day, S.RaiseDate, GETDATE()) > 365 catches everyone else: a raise more than a year old.

Running this against the sample data gives every one of the five employees a 10% raise, since Duck, Jones, and Fredericks all have raises more than 365 days old, and Mickey and Minnie have none on record at all:

The LEFT JOIN pattern above is doing real work, and it brings two risks along with it:

• Unintended NULLs. If you forget to account for unmatched rows, either by filtering them out in the WHERE clause or by wrapping the assignment in COALESCE() or ISNULL(), a LEFT JOIN can quietly set a target column to NULL wherever there's no matching source row. The worked example avoids this by using the unmatched case on purpose, but it's an easy trap to fall into by accident.
• Non-deterministic multi-matches. If a single target row matches more than one source row, SQL Server does not raise an error. It silently picks one of the matching source rows, and which one it picks can depend on the query's execution plan. If your join key isn't guaranteed unique on the source side, either enforce that uniqueness, pre-aggregate the source data first, or use MERGE instead, covered below, which refuses to guess.

UPDATE ... FROM is SQL Server-specific syntax. If you want portable, ANSI-standard SQL, or you want SQL Server to raise an explicit error if the source table unexpectedly has duplicate keys, a correlated subquery in the SET clause is the alternative:

UPDATE Employee
SET CurrentSalary = (
    SELECT S.PriorSalary * 1.1
    FROM SalaryHistory AS S
    WHERE S.LastName = Employee.LastName
      AND S.FirstName = Employee.FirstName
)
WHERE EXISTS (
    SELECT 1
    FROM SalaryHistory AS S
    WHERE S.LastName = Employee.LastName
      AND S.FirstName = Employee.FirstName
      AND DATEDIFF(day, S.RaiseDate, GETDATE()) > 365
);

The WHERE EXISTS clause is doing essential work here, not just filtering: without it, every Employee row with no matching SalaryHistory row would have CurrentSalary overwritten with NULL, since the subquery in the SET clause returns nothing for those rows. The trade-off against UPDATE ... FROM is that each column you want to update from the source table needs its own correlated subquery; updating three columns means writing the same correlation logic three times, where UPDATE ... FROM reads the source row once.

Where UPDATE ... FROM silently picks a row when a target row matches more than one source row, MERGE refuses to guess. If the source side of a MERGE matches a single target row more than once, SQL Server stops the statement entirely with error 8672, "The MERGE statement attempted to UPDATE or DELETE the same row more than once":

MERGE INTO Employee AS E
USING SalaryHistory AS S
   ON E.LastName = S.LastName AND E.FirstName = S.FirstName
WHEN MATCHED AND DATEDIFF(day, S.RaiseDate, GETDATE()) > 365 THEN
    UPDATE SET E.CurrentSalary = E.CurrentSalary * 1.1;

This version only updates employees who already have a SalaryHistory row; it would need a second WHEN NOT MATCHED BY SOURCE clause to also reach Mickey and Minnie. MERGE's real strength here isn't reaching every row, it's the guarantee that if your join key ever turns out not to be unique, you get a clear error instead of a silently wrong update.

Sometimes the value you want to apply isn't a plain column from the source table, it's something you need to calculate first: a total, a count, a most-recent date. Wrapping that calculation in a Common Table Expression (CTE) keeps the logic readable and lets you update straight through it:

WITH RaiseCountCTE AS (
    SELECT LastName, FirstName, COUNT(*) AS TimesRaised
    FROM SalaryHistory
    GROUP BY LastName, FirstName
)
UPDATE E
SET E.CurrentSalary = E.CurrentSalary * 1.1
FROM Employee AS E
JOIN RaiseCountCTE AS R
  ON E.LastName = R.LastName AND E.FirstName = R.FirstName
WHERE R.TimesRaised >= 1;

This isn't needed for the WSS example above, a plain join already answers "has this employee had a raise recently," but it's the right tool once the update condition depends on an aggregate like a running total or a row count rather than a single matching row.

To record both the old and new value during a cross-table update, without running a separate SELECT before and after, add an OUTPUT clause between SET and FROM, reading from the deleted and inserted virtual tables:

UPDATE E
SET E.CurrentSalary = E.CurrentSalary * 1.1
OUTPUT
    deleted.LastName,
    deleted.FirstName,
    deleted.CurrentSalary AS OldSalary,
    inserted.CurrentSalary AS NewSalary,
    GETDATE() AS ChangedAt
INTO SalaryChangeAudit (LastName, FirstName, OldSalary, NewSalary, ChangedAt)
FROM Employee AS E
LEFT JOIN SalaryHistory AS S
  ON E.LastName = S.LastName AND E.FirstName = S.FirstName
WHERE DATEDIFF(day, S.RaiseDate, GETDATE()) > 365
   OR S.RaiseDate IS NULL;

In an UPDATE, deleted holds each row's values before the change and inserted holds the values after, so pairing them in one OUTPUT list gives a before-and-after record of exactly what changed, in one pass.

Ready to practice? The exercise below gives you another scenario for updating one table using values from another, so you can work through the join, NULL-handling, and alternative-syntax questions on your own.
Updating Data Values - Exercise In the next lesson, you will learn how to delete data using values from another table.