1

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

1 Answer 1

0

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

4
  • 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? Commented Jul 7, 2016 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
    Commented Jul 7, 2016 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! Commented Jul 7, 2016 at 15:22
  • @AdelsonDias: Oh, glad to help.
    – Arulkumar
    Commented Jul 7, 2016 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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