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

Question

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';

the query returned succesfully but with 0 rows affected and this is not possible. my question is: there is some error in the code? — Luigi Falco
– Luigi Falco, Commented Jul 29, 2019 at 11:21
Why is this not possible? If the SELECT returns 0 rows, there is no data that matches the WHERE condition. Check it. — user11044402
– user11044402, Commented Jul 29, 2019 at 11:22
Obviously it's not "impossible" as it apparently happened to you ;) — user330315
– user330315, Commented Jul 29, 2019 at 11:26

user330315 · Accepted Answer · 2019-07-29 11:27:33Z

1

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.

answered Jul 29, 2019 at 11:27

user330315

Sign up to request clarification or add additional context in comments.

Comments

Ryan Nghiem · Accepted Answer · 2019-07-30 01:39:33Z

0

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

edited Jul 30, 2019 at 1:39

answered Jul 29, 2019 at 11:22

Ryan Nghiem

2,4482 gold badges21 silver badges31 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related