Update a table's field using another table's field to map it with third table in Postgresql
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.
WITH possible_joins AS (
, COALESCE(l1.region, l2.region) AS region
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
home_region = p.region
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.
Asked in February 2016Viewed 2,904 timesVoted 9Answered 1 times