By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table master_table (
_id int,
zipcode int
);
insert into master_table values
(123, 100),
(456, 200);
create table temp_table (
_id int,
zipcode int
);
insert into temp_table values
(123, 111),
(245, 222);
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 2
select m._id, t.zipcode as new_zipcode
from master_table m
inner join temp_table as t on t._id = m._id and t.zipcode <> m.zipcode
| _id | new_zipcode |
|---|---|
| 123 | 111 |
SELECT 1
with t as (
select m._id, t.zipcode as new_zipcode
from master_table m
inner join temp_table as t on t._id = m._id and t.zipcode <> m.zipcode
)
update master_table m
set zipcode = t.new_zipcode
from t
where m._id = t._id
UPDATE 1
select *
from master_table;
| _id | zipcode |
|---|---|
| 456 | 200 |
| 123 | 111 |
SELECT 2