# SQL Server UPDATE with JOIN Based on ID Match

> Update rows in one SQL Server table from another using UPDATE with JOIN or a subquery, with examples for matching records by ID across two tables

**URL:** https://sentry.io/answers/how-can-i-do-an-update-statement-with-join-in-sql-server-based-on-an-id-match/

---

## The Problem

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`.

```sql
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:

```sql
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:

```sql
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:

```sql
UPDATE PersonArchive
SET Name =
(
    SELECT Name
    FROM Person
    WHERE PersonArchive.Id = Person.Id
);
```

---

*Source: [sentry.io/answers/how-can-i-do-an-update-statement-with-join-in-sql-server-based-on-an-id-match/](https://sentry.io/answers/how-can-i-do-an-update-statement-with-join-in-sql-server-based-on-an-id-match/)*