SQL Server Tips

SQL UPDATE from SELECT in SQL Server: JOIN, MERGE and CTE

Updated November 20, 20269 min read

You ran a SELECT, and now you need to UPDATE those same rows. Or you are about to run an UPDATE and want to see which rows it will hit first.

That second one is how I work every time. I write the SELECT with its FROM and JOIN, check the rows, then turn that SELECT into the UPDATE. Below are the four ways to do it in SQL Server, plus the one trap to watch for.

I ran these scripts on SQL Server 2022. They should work on any supported SQL Server version. The one exception is IS DISTINCT FROM, which I point out where it comes up.

Start with the SELECT, then turn it into an UPDATE

This small setup has a products table and a price feed. The feed covers three of the four products.

T-SQL
CREATE TABLE dbo.Products
(
    ProductID   int           NOT NULL PRIMARY KEY,
    ProductName nvarchar(50)  NOT NULL,
    ListPrice   decimal(10,2) NULL
);
INSERT dbo.Products (ProductID, ProductName, ListPrice) VALUES
 (1, N'Widget',   9.99),
 (2, N'Gadget',  19.99),
 (3, N'Gizmo',    4.50),
 (4, N'Doohickey', NULL);

CREATE TABLE dbo.PriceFeed
(
    ProductID int           NOT NULL PRIMARY KEY,
    NewPrice  decimal(10,2) NULL
);
INSERT dbo.PriceFeed (ProductID, NewPrice) VALUES
 (1, 10.99),
 (2, 19.99),   -- same as today
 (4, 7.25);    -- product 3 has no feed row

First, the SELECT. It shows the rows the update will change and the new values:

T-SQL
SELECT p.ProductID, p.ListPrice AS OldPrice, f.NewPrice
FROM dbo.Products AS p
INNER JOIN dbo.PriceFeed AS f ON f.ProductID = p.ProductID;
ProductIDOldPriceNewPrice
19.9910.99
219.9919.99
4NULL7.25

Product 3 is not in the list, because it has no feed row. So the update will leave it alone.

Two tables, Products with ProductID 1 to 4 and PriceFeed with ProductID 1, 2 and 4, joined by lines on matching IDs. Product 3 has no line and is outlined in red.

Option 1: UPDATE with FROM and a JOIN

Keep the FROM and JOIN from your SELECT. Swap the SELECT line for UPDATE and SET. This is the one I use most:

T-SQL
BEGIN TRANSACTION;

UPDATE p
SET    p.ListPrice = f.NewPrice
FROM   dbo.Products  AS p
INNER JOIN dbo.PriceFeed AS f ON f.ProductID = p.ProductID;

SELECT @@ROWCOUNT AS rows_updated;   -- 3

SELECT * FROM dbo.Products;

ROLLBACK TRANSACTION;   -- change to COMMIT TRANSACTION when the numbers look right

UPDATE p names the alias of the table that changes. The rest is your SELECT, unchanged.

It reports 3 rows. Products 1 and 4 get new prices, and product 3 keeps 4.50. Product 2 counts too, even though its price did not change. Option 1b below fixes that.

The transaction lets you look at the result before you keep it.

Option 1b: skip rows that already match

Add IS DISTINCT FROM to update only the rows whose value really changes:

T-SQL
BEGIN TRANSACTION;

UPDATE p
SET    p.ListPrice = f.NewPrice
OUTPUT inserted.ProductID, deleted.ListPrice AS OldPrice, inserted.ListPrice AS NewPrice
FROM   dbo.Products  AS p
INNER JOIN dbo.PriceFeed AS f ON f.ProductID = p.ProductID
WHERE  p.ListPrice IS DISTINCT FROM f.NewPrice;

SELECT @@ROWCOUNT AS rows_updated;   -- 2

ROLLBACK TRANSACTION;   -- change to COMMIT TRANSACTION when the numbers look right
ProductIDOldPriceNewPrice
19.9910.99
4NULL7.25

Now only 2 rows change. This matters if your table has triggers: a same-value update still fires them. In my test, an AFTER UPDATE trigger saw 3 rows without the filter and 2 with it.

IS DISTINCT FROM compares NULL like any other value, so row 4 (NULL to 7.25) still updates. It needs SQL Server 2022 or later. On older versions, use the EXISTS ... EXCEPT filter from Option 2.

The OUTPUT clause lists the old and new value of each changed row. It is the quickest way to check the result.

Option 2: UPDATE through a CTE

Put the SELECT in a CTE, run it on its own to check it, then update the CTE. The change goes to the base table.

T-SQL
WITH Changes AS
(
    SELECT c.Email, i.Email AS NewEmail,
           c.Phone, i.Phone AS NewPhone
    FROM   dbo.Customers AS c
    JOIN   dbo.CustomerImport AS i ON i.CustomerID = c.CustomerID
    -- skip rows that already match; EXCEPT treats two NULLs as equal
    WHERE  EXISTS (SELECT c.Email, c.Phone
                   EXCEPT
                   SELECT i.Email, i.Phone)
)
UPDATE Changes
SET    Email = NewEmail,
       Phone = NewPhone;

Here a customer table takes new emails and phone numbers from an import table. The WHERE EXISTS (... EXCEPT ...) line skips rows that already match, and it works on any version.

In my test, two rows changed and two matching rows stayed as they were. A CTE takes a few more lines than the join, because columns with the same name need an alias (NewEmail).

Option 3: UPDATE with a subquery

You can also set the column from a subquery. Add WHERE EXISTS, or rows with no match get NULL.

T-SQL
UPDATE dbo.Products
SET    ListPrice = (SELECT f.NewPrice FROM dbo.PriceFeed AS f WHERE f.ProductID = dbo.Products.ProductID)
WHERE  EXISTS (SELECT 1 FROM dbo.PriceFeed AS f WHERE f.ProductID = dbo.Products.ProductID);

Without the WHERE EXISTS line, product 3 went from 4.50 to NULL in my test. A LEFT JOIN in Option 1 does the same.

Two panels for product 3, which has no price feed row: the subquery without WHERE EXISTS changes its ListPrice from 4.50 to NULL, and with WHERE EXISTS the price stays 4.50.

Option 4: MERGE

MERGE matches a source to a target. It can update, insert and delete in one statement.

T-SQL
MERGE dbo.Products AS p
USING dbo.PriceFeed AS f
   ON f.ProductID = p.ProductID
WHEN MATCHED AND p.ListPrice IS DISTINCT FROM f.NewPrice THEN
    UPDATE SET p.ListPrice = f.NewPrice
OUTPUT $action AS merge_action, inserted.ProductID, deleted.ListPrice AS OldPrice, inserted.ListPrice AS NewPrice;

On the sample tables it updates products 1 and 4 and leaves 2 and 3 alone. End it with a semicolon, or SQL Server stops with error 10713.

MERGE looks attractive, but I rarely use it:

  • I use it for one-time jobs. A quick fix or a one-off data load is fine.
  • I do not use it in a load that runs often or works on big tables. MERGE can be slow there.
  • MERGE loads often get faster right away once rewritten. I have seen slow data warehouse loads built on MERGE. When we rewrote them as separate UPDATE, INSERT and DELETE statements, they ran faster at once.

More on that in our guide to ETL process optimization.

Watch for one row matching several source rows

If one target row matches several source rows, UPDATE ... FROM picks one value and gives no error. Microsoft says the result is undefined.

This price history table has two rows for product 1:

T-SQL
CREATE TABLE dbo.PriceHistory
(
    ProductID int           NOT NULL,
    NewPrice  decimal(10,2) NOT NULL,
    ChangedOn date          NOT NULL,
    CONSTRAINT PK_PriceHistory PRIMARY KEY (ProductID, ChangedOn)
);
INSERT dbo.PriceHistory VALUES
 (1, 10.49, '2026-11-01'),
 (1, 10.99, '2026-11-10'),   -- the one we want: newest
 (2, 21.00, '2026-11-05');
Products rows 1 and 2 joined to PriceHistory. Two red lines leave product 1 toward the rows 10.49 and 10.99, with the label that one value wins and SQL Server does not say which. Product 2 has one line to 21.00.

Run the join update against it:

T-SQL
UPDATE p
SET    p.ListPrice = h.NewPrice
FROM   dbo.Products AS p
INNER JOIN dbo.PriceHistory AS h ON h.ProductID = p.ProductID;

Product 1 got 10.49, the older price, and SQL Server said nothing. The row count was 2, which looks right.

The other options fail loudly on the same data:

MethodWhat happened
Option 1, UPDATE ... FROMNo error. Product 1 got 10.49.
Option 3, subqueryError 512: the subquery returned more than 1 value. No row changed.
Option 4, MERGEError 8672: it tried to update the same row more than once. No row changed.

Fix it by keeping one source row per key. Here the newest price wins:

T-SQL
WITH Latest AS
(
    SELECT ProductID, NewPrice,
           ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ChangedOn DESC) AS rn
    FROM   dbo.PriceHistory
)
UPDATE p
SET    p.ListPrice = l.NewPrice
FROM   dbo.Products AS p
INNER JOIN Latest AS l ON l.ProductID = p.ProductID AND l.rn = 1;

Now product 1 gets 10.99 and product 2 gets 21.00, every time. To find the problem keys before you update, run this on the source:

T-SQL
SELECT ProductID, COUNT(*) AS source_rows
FROM   dbo.PriceHistory
GROUP BY ProductID
HAVING COUNT(*) > 1;

Check the result before you commit

Use this order every time:

  1. Run the SELECT and read the rows.
  2. Run the duplicate-key check on the source.
  3. Start a transaction, run the UPDATE with OUTPUT, and compare @@ROWCOUNT with the SELECT.
  4. Commit only when they match.

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜