I'm using Postgres 9.4, and already seen others asking the same (simple) question, but theirs answers do not solved my problem and I can't see why. Suppose that I have these table A with common data and what type these data correspond, and consequently, a indicator (which I want to add that is in another table B):

 gid, type,  indicator
    1       a      ''
    2       b      ''
    3       e      ''
    4       a      ''
    5       d      ''

And this table B, which describes types and their respectives indicators

 type, indicator
    a       alfa
    b       alfa
    c       beta
    d       beta
    e       gama

I'm doing this query, but getting indicator=alfa for every row.

UPDATE A
SET    indicator = B.indicator
FROM   B
JOIN   A aa ON B.type = aa.type 

The result that I was looking for A is something like this:

 gid, type,  indicator
    1       a      alfa
    2       b      alfa
    3       e      gama
    4       a      alfa
    5       d      beta

Hope that was clear

up vote 0 down vote accepted

Update Syntax for PostgreSQL is different:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

In your case please try the below query:

UPDATE TableA AS A
SET indicator = B.indicator 
FROM TableB AS B
WHERE B.type = A.type

Result:

gid     type    indicator
--------------------------
1       a       alfa
2       b       alfa
3       e       gama
4       a       alfa
5       d       beta

SQLFiddle working DEMO

  • Ok you're right. It works with fake data, but with my real data (it's the same structure), it just putting one value of indicator, like, just gama is been updated. See, there is a natural join between A and B after your update statement: prntscr.com/bpt6ut. It's in portuguese, but with many cognates for types and indicators. You can see why AMEACA is not with CCIP indicator, for an example? – Adelson Dias Jul 7 '16 at 5:57
  • @AdelsonDias: Could you create a SQLFiddle for the same (with two table actual entries), it will helpful to solve the issue. – Arulkumar Jul 7 '16 at 6:08
  • @Arulkmar Ok it' working fine... When I was copying to SQLFiddle I noted that type indicator has a space after, like 'a ', needing a trim() to fix it. Thanks for the help anyway! – Adelson Dias Jul 7 '16 at 15:22
  • @AdelsonDias: Oh, glad to help. – Arulkumar Jul 7 '16 at 15:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.