How can I update only changed values from another table in PostgreSQL 9.4?

user_18797972 0
Viewed 83

I have this query but updates the whole region_tmp table, which I dont want to, I only need to update a row in region table if anything has changed. I know I can specify which row with the regionid but am looking for something more general since I have a lot of records.

UPDATE REGION_TMP t SET
      REGIONID=R.REGIONID,
      REGIONDESCRIPTION=R.REGIONDESCRIPTION
FROM REGION R
WHERE R.REGIONID = T.REGIONID;

REGION TABLE:

REGIONID REGIONDESCRIPTION
1 AMERICA
2 EUROPE
3 ASIA

REGION_TMP TABLE:

REGIONID REGIONDESCRIPTION
1 AMERICA
2 EUROPE
3 AFRICA

My desire output in REGION_TMP:

REGIONID REGIONDESCRIPTION
1 AMERICA
2 EUROPE
3 ASIA
sql-update postgresql sql
1 Answers

My guess is that you need to create a duplicate of your REGION table, but you already have a REGION_TMP table, so you just want to update the values that do not match.

In order to achieve that, you need to join the two tables inside the UPDATE statement and replace the values of REGION_TMP.REGIONDESCRIPTION with the corresponding REGION.REGIONDESCRIPTION where the REGION_ID applies for both and the REGIONDESCRIPTION is different:

UPDATE REGION_TMP
SET REGIONDESCRIPTION = REGION.REGIONDESCRIPTION
FROM REGION
WHERE REGION_TMP.REGIONID = REGION.REGIONID
  AND REGION_TMP.REGIONDESCRIPTION <> REGION.REGIONDESCRIPTION;

Check the demo here.

user_124928900
Related