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,
column2 int
);
insert into master_table values
(123, 100,1000),
(456, 200,1000);
create table temp_table (
_id int,
zipcode int,
column2 int
);
insert into temp_table values
(123, 111, 1100),
(245, 222, 1100),
(456, 222, 1100);
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 3
select m._id, t.zipcode as new_zipcode, t.column2 as new_column2
from master_table m
inner join temp_table as t on t._id = m._id and ( t.zipcode <> m.zipcode or t.column2 <> m.column2)
| _id | new_zipcode | new_column2 |
|---|---|---|
| 123 | 111 | 1100 |
| 456 | 222 | 1100 |
SELECT 2
with t as (
select m._id, t.zipcode, t.column2
from master_table m
inner join temp_table as t on t._id = m._id and ( t.zipcode <> m.zipcode or t.column2 <> m.column2)
)
update master_table m
set zipcode = t.zipcode, column2 = t.column2
from t
where m._id = t._id
UPDATE 2
select *
from master_table;
| _id | zipcode | column2 |
|---|---|---|
| 123 | 111 | 1100 |
| 456 | 222 | 1100 |
SELECT 2