clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591614 fiddles created (45704 in the last week).

CREATE TABLE A ( id int PRIMARY KEY , column1 int NOT NULL -- NOT NULL! , column2 int NOT NULL , column3 int NOT NULL ); CREATE TABLE B ( id int PRIMARY KEY , column1 int -- can be NULL! , column2 int , column3 int , column4 int ); INSERT INTO A VALUES (1,1,1,9) , (2,4,4,4) , (3,5,6,7); INSERT INTO B VALUES (1,1,1,1,1) , (2,2,2,2,2) , (3,3,3,3,3);
3 rows affected
3 rows affected
 hidden batch(es)


-- All columns NOT NULL! UPDATE b SET (column1, column2, column3, column4) = (COALESCE(ab.column1, b.column1) , COALESCE(ab.column2, b.column2) , COALESCE(ab.column3, b.column3) , COALESCE(ab.column4, b.column4) ) FROM ( SELECT * FROM a NATURAL LEFT JOIN b -- append missing columns WHERE a.id = 1 AND b.id IS NULL -- only if anything actually changes ) ab WHERE b.id = ab.id RETURNING b.*;
id column1 column2 column3 column4
1 1 1 9 1
 hidden batch(es)


-- Result SELECT * FROM b ORDER BY id;
id column1 column2 column3 column4
1 1 1 9 1
2 2 2 2 2
3 3 3 3 3
 hidden batch(es)