Sentry Answers>SQL Server>

How do I UPDATE from a SELECT in SQL Server?

How do I UPDATE from a SELECT in SQL Server?

Richard C.

The ProblemJump To Solution

In Microsoft SQL Server, you might know how to update a table like this:

Click to Copy
UPDATE Product SET Price = 600 WHERE Id = 3;

But how do you update a table with values from other tables? In other words, how do you use a SELECT statement in an UPDATE statement?

The Solution

Let’s create an example database to demonstrate the problem. We have a Product table, where each product we sell has a code and price in dollars:

Click to Copy
CREATE TABLE Product ( Id INT PRIMARY KEY, Name NVARCHAR(255), Code NVARCHAR(4), Price FLOAT ); INSERT INTO Product(Id, Name, Code, Price) VALUES (1, 'Table', 'TABL', 100), (2, 'Chair', 'CHAR', 50), (3, 'Desk', 'DESK', 150);

We want to update our Product table with new prices from the PriceUpdate table below:

Click to Copy
CREATE TABLE PriceUpdate ( Id INT PRIMARY KEY, Code NVARCHAR(4), Price FLOAT ); INSERT INTO PriceUpdate(Id, Code, Price) VALUES (89, 'TABL', 110), (98, 'CHAR', 65), (45, 'DESK', 155);

Use a Join Operation

You probably learned how to use joins when learning to write SELECT statements in SQL. You can use them in an UPDATE statement too. In the following code, we update our Product table with the new prices by matching against the PriceUpdate table on the shared Code column:

Click to Copy
UPDATE P SET P.Price = U.Price FROM Product P JOIN PriceUpdate U ON P.Code = U.Code;

Note that you have to give both tables an alias (Product P) so that SET knows which table you want to update. If your tables have thousands of rows, you’ll also want to add an index to both Code columns so that your query will run quickly.

Using Subqueries

Alternatively, you could update using a subquery:

Click to Copy
UPDATE Product SET Price = (SELECT Price FROM PriceUpdate WHERE Code = Product.Code);

In general, you should use joins instead of subqueries. Joins are usually faster, although speed can vary depending on the exact tables and query you are using. If in doubt, always check the query execution plan in SQL Server.

Use a MERGE Statement

Finally, if you want to do a complex query, combining UPDATE, INSERT, and DELETE, you can use the MERGE statement. It’s been available since SQL Server 2008.

Click to Copy
MERGE INTO Product P USING PriceUpdate U ON P.Code = U.Code WHEN MATCHED THEN UPDATE SET P.Price = U.Price WHEN NOT MATCHED THEN INSERT (Code, Price) VALUES (U.Code, U.Price);

In this example, the WHEN NOT MATCHED clause isn’t used and can be removed, but you can see how it might be useful. This solution is the most specific to SQL Server, so you’ll want to avoid using it if you need to run your queries on MySQL or PostgreSQL in the future.

  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Considered “not bad” by 4 million developers and more than 100,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.