Sentry Answers>SQL Server>

How can I do an UPDATE statement with JOIN in SQL Server, based on an Id match?

How can I do an UPDATE statement with JOIN in SQL Server, based on an Id match?

Richard C.

The ProblemJump To Solution

In SQL Server, how do you update rows in one table with data from rows in another? You might want to join two tables in your update, where one table contains more recent or authoritative data than the other. The tables often share an Id or some type of account number that you can match your records against.

Assume you have two tables. One called Person that is kept up to date with correct details. The other table is called PersonArchive and is updated monthly with recent details from Person.

Click to Copy
CREATE TABLE Person ( Id INT PRIMARY KEY, Name VARCHAR(255) ); CREATE TABLE PersonArchive ( Id INT PRIMARY KEY, Name VARCHAR(255) ); INSERT INTO Person(Id, Name) VALUES (1, 'Amir'), (2, 'Sofia'), (3, 'Aya'), (4, 'Mateo'), (5, 'Leila'), (6, 'Yara'), (7, 'Ndidi'), (8, 'Santiago'); INSERT INTO PersonArchive(Id, Name) VALUES (1, 'Abir'), (2, 'Sofya'), (3, 'Aya'), (4, 'Mateo'), (5, 'Leila'), (6, 'Yara'), (7, 'Ndidi'), (8, 'Santiago');

The Solution

Below is an update query that uses a join to update the archive table from the reference table:

Click to Copy
UPDATE old SET old.Name = new.Name FROM PersonArchive old JOIN Person new ON old.id = new.id;

To make the query more readable you could use a WHERE clause instead of a join:

Click to Copy
UPDATE old SET old.Name = new.Name FROM PersonArchive old, Person new WHERE old.id = new.id;

The queries above are likely to be fast. However, they will work only in SQL Server. Each database server’s update syntax is slightly different.

Instead, you can update your table using a subquery, which will work in all database servers, but might run more slowly on large tables:

Click to Copy
UPDATE PersonArchive SET Name = ( SELECT Name FROM Person WHERE PersonArchive.Id = Person.Id );
  • 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.