Update a table when join with other table using Case in Postgresql

Question

I have 2 tables wherein I need the result as follows :
If the country name is 'United States' then the region should be hardcoded as 'Mexico' & if the country name is 'Taiwan' then the region should be hardcoded as 'China' and for the rest it should be joined with the other table.
I have tried this query in Postgresql:

CASE 
    WHEN test.hypno.country_name hp RETURN 'United States'
        THEN UPDATE test.expected_details ed SET ed.place = 'Mexico'
    ELSE UPDATE test.expected_details ed 
         SET ed.place = hp.area_region 
         WHERE ed.WORK = hp.country_name
END

How do I include another hardcode case for country 'Taiwan'?
Also, my query doesn't seem to be working in Postgresql.

You should include names and schema for all the table required for the query. Please read http://stackoverflow.com/help/how-to-ask Here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) — Juan Carlos Oropeza, Feb 07 '16 at 14:04
test is the schema name, hypno and expected_details are table names — user1538020, Feb 07 '16 at 14:51

score 3 · Accepted Answer · edited May 23 '17 at 12:13

3

I cant do the full query because you miss the schema, not sure if you alias hp = hypno or what field join both tables. But you want something like this

How to do an update + join in PostgreSQL?

UPDATE test.expected_details ed 
SET ed.place = CASE WHEN hp.country_name = 'United States' THEN 'Mexico'
                    WHEN hp.country_name = 'Taiwan' THEN 'China'
                    ELSE hp.area_region 
               END
FROM test.hypno hp
WHERE ed.work = hp.country_name

edited May 23 '17 at 12:13

Community

1
1

answered Feb 07 '16 at 14:09

Juan Carlos Oropeza

47,252
12
78
118

test is the schema name, hypno and expected_details are table names.. hp is the hypno table. I am joining using countries i.e. ed.work = hp.country_name. I tried with the above code but I am getting an error : Syntax error at or near FROM. – user1538020 Feb 07 '16 at 15:08
the "end" was missing from the case statement. But now I am getting an error of "ed" of relation expected_details does not exist – user1538020 Feb 07 '16 at 15:21
Worked fine now... I removed table alias ed from SET operation and made it to SET place = ... Thanks ! – user1538020 Feb 07 '16 at 15:25
I make all your correction, except remove `ed` alias, not sure why give you error let me know how looks now. remember the upvote – Juan Carlos Oropeza Feb 07 '16 at 16:09

Update a table when join with other table using Case in Postgresql

1 Answers1