CODEWITHSUNDEEP
postgresql
Luigi Falco
Luigi Falco

Reputation: 65

postgresql update a table column based on values stored in another table

I have two tables. I want to update the emodnet_code column values of the table named 2018_01 based on the column emodnet_type of another table named shiptype_emodnet and using the matching of values of two other columns: column aisshiptype from 2018_01 table and column aisshiptype from shyptype_emodnet table. Query returned successfully but 0 rows affected: 

UPDATE "2018_01"
SET emodnet_code = shiptype_emodnet.emodnet_type
FROM "shiptype_emodnet" 
WHERE '2018_01.aisshiptype' = 'shiptype_emodnet.aisshiptype';

Upvotes: 0

Views: 822

Answers (2)

Ryan Nghiem
Ryan Nghiem

Reputation: 2458

Can you try:

UPDATE "2018_01" t
SET t.emodnet_code = (SELECT shiptype_emodnet.emodnet_type 
                    FROM shiptype_emodnet 
                    WHERE t.aisshiptype = shiptype_emodnet.aisshiptype
                    Limit 1);

You should add limit 1 for update each row

Upvotes: 0

user330315
user330315

Reputation:

You are comparing string constants in your WHERE clause, not columns. So your where clause: 

WHERE '2018_01.aisshiptype' = 'shiptype_emodnet.aisshiptype';

is always false, because the string literal '2018_01.aisshiptype' is never the same as the string literal 'shiptype_emodnet.aisshiptype'. So your where condition is essentially the same as:

where false

Identifiers need to be quoted with double quotes ("). Single quotes (') are only for string literals.

UPDATE "2018_01"
  SET emodnet_code = shiptype_emodnet.emodnet_type
FROM "shiptype_emodnet" 
WHERE "2018_01".aisshiptype = shiptype_emodnet.aisshiptype;

And you only need the double quotes for columns or tables that use names that are illegal in SQL or were created using double quotes and mixed case.

Upvotes: 1

Related Questions