user1538020 February 2016

Update a table's field using another table's field to map it with third table in Postgresql

enter image description here

I have 3 tables : landmark, trans and output. Schema is test schema. Green are the tables,Blue highlighted are the columns. Mapping can be done between a country and a region.
I have home_country which is having a home_region as null right now.I need to find the value for the home_region for that home_country which has home region as null.

The table should get updated
if landmark.pgs_country = output.home_country then set landmark.region = output.home_region.

But if it finds no match for the country US-GM in the output table with the landmark - country_name then it should map it with trans table to check

if output.home_country = trans.ib_country, then take trans.xperia_country for that match and equate it with landmark.country_name ie trans.xperia_country = landmark.country_name and set landmark.region = output.home_region to find the home_region for the country US-GM. So basically the update must set landmark.region = output.home_region.

How do I update this for the output table?


MyBrainHurts February 2016

Like this:

WITH possible_joins AS (
    , COALESCE(l1.region, l2.region) AS region
    output o
    LEFT JOIN landmark l1 ON l1.pgs_country = o.home_country
    LEFT JOIN trans t ON t.ib_country = o.home_country
    LEFT JOIN landmark l2 ON l2.country_name = t.xperia_country
    o.home_region IS NULL
    output o
    home_region = p.region
    possible_joins p
    o.home_country = p.home_country
    AND o.home_region IS NULL;

This could be done in a much easier way, if you could define more rules in your model, meaning impose a better integrity. I'm clearly not saying 'you have to', I even suppose you can't (or maybe this hole thing is about working on it in the first place). I just want to explain, the use of those left joins, as it seems that you can't take anything for granted.

Post Status

Asked in February 2016
Viewed 2,904 times
Voted 9
Answered 1 times


Leave an answer