Question 89455 in PostgreSQL Question posted in
The official documentation can be found here.

I wish to upsert all the column values from my temp_table to master_table in SQL – Postgresql

master_table:

_id zipcode
123 100
456 200

temp_table:

_id zipcode
123 111
245 222

master_table (new results must be upserted in master_table)

_id zipcode
123 111
456 200

my end goal is to insert records from temp_table to master_table and update existing records in master_table found in temp_table.

I have just given a sample table, It is not just the zipcode column which needs to be updated. there are many such columns (20+), all of them need to be updated.
just for a note : both the tables have same no. of columns.

Tags:

2

Answers


  1. 0

    Use an update join:

    UPDATE master_table AS m
SET zipcode = t.zipcode
FROM temp_table t
WHERE m._id = t._id
    Login or Signup to reply.
  2. 0

    You can do it using WITH to get data that need to be updated only.

    This approach lets you develop and test your select query and in two steps convert it to the update query.

    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

    Demo here

    To update multi columns just add them in inner join, and of course add them in the SET clause :

    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

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search