Update column with values from another table if ID exists in another table-CodePudding

I have two tables,

Table1:

 ---- ------- 
| ID | Value |
 ---- ------- 
| 1  |   A   |
| 2  |   B   |
| 3  |   C   |
| 4  |   D   |
 ---- -------

Table2:

 ---- ------- 
| ID | Value |
 ---- ------- 
| 3  |   E   |
| 4  |   F   |
 ---- -------

Table1 after update

 ---- ------- 
| ID | Value |
 ---- ------- 
| 1  |   A   |
| 2  |   B   |
| 3  |   E   |
| 4  |   F   |
 ---- -------

I want to update all 'Value' columns in table 1 where there is a matching ID in table 2, and leave the rest of the values who do not have a matching ID in table 2 to be left alone, as in the example above.

Essentially the following:

UPDATE Table1
SET Value = (SELECT Value FROM Table2 
             WHERE Table1.[ID] = Table2.[ID])

Except this makes all values in Table1 NULL that are not present in Table2. I want this to remain the same.

I've accomplished this before easily using an

UPDATE

with a

CASE WHEN

but I cannot remember exactly what it was.

CodePudding user response：

Use an INNER JOIN not a subquery. This will implicitly filter to only rows where the related row is found:

UPDATE T1
SET [Value] = T2.Value
FROM dbo.Table1 T1
     JOIN dbo.Table2 T2 ON T1.ID = T2.ID;

db<>fiddle