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?