Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


-- create table tabletobeupdated
create table tabletobeupdated (
    z character varying(5),
    id integer,
    y integer
);

-- insert data into tabletobeupdated
insert into tabletobeupdated (z, id, y)
values
    ('one', 1, 50),
    ('two', 2, 30),
    ('three', 3, 30),
    ('four', 4, 60),
    ('five', 5, 70),
    ('six', 6, 80);

CREATE TABLE



INSERT 0 6


-- create table tableb
create table tableb (
    id integer,
    newy integer
);

-- insert data into tableb
insert into tableb (id, newy)
values
    (1, 500),
    (2, 233),
    (3, 300),
    (4, 472),
    (5, 111);

CREATE TABLE



INSERT 0 5


-- UPDATE USING COALESCE() FUNCTION
update tabletobeupdated
set y = coalesce(
                  (select newy
                     from tableb
                    where tabletobeupdated.id = tableb.id
                  )
                  , y
                );

UPDATE 6


-- RESULT
select * from tabletobeupdated


z
id
y


one
1
500

two
2
233

three
3
300

four
4
472

five
5
111

six
6
80



SELECT 6