Replace value in cell from table A with values from table B, if id matches-CodePudding

I have

-- table_a --

id value
1  a 
2  b
3  c

-- table_b -- 

id value
1  NULL
2  NULL
3  NULL
4  d
5  e
6  f
7  g

I want the result set to look like

-- result_set -- 

id value
1  a
2  b
3  c
4  d
5  e
6  f
7  g

I know that I can do an

UPDATE table_b SET value = 
    CASE 
        WHEN id = 1 THEN 'a'
        WHEN id = 2 THEN 'b'
        WHEN id = 3 THEN 'c'
    END;

But, what if I have many rows to update? Is there an automatic way?

CodePudding user response：

You can join the tables in the UPDATE statement:

UPDATE table_b b
SET value = a.value
FROM table_a a
WHERE a.id = b.id AND b.value IS NULL;