CODEWITHSUNDEEP
postgresql
Erna Piantari
Erna Piantari

Reputation: 617

update column from another column with conditional

I have table1 and tbl_cat. I want to update value of x where animal is cat

table1
id    id_animal    animal    x
2          1        cat      3
3          2        cat      5
4          1        dog      7
5          2        dog      8
6          3        dog      9

tbl_cat
id    x
1    10
2    30

Result Expectation:

table1
id    id_animal    animal    x
2          1        cat      10
3          2        cat      30
4          1        dog      7
5          2        dog      8
6          3        dog      9

I use this query, but it's not work:

update table1
set table1.x = tbl_cat.x  
from table1 inner join tbl_cat
on (table1.id_animal=tbl_cat.id)
where table1.animal='cat'

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The correct syntax in Postgres is:

update table1
    set table1.x = tbl_cat.x  
    from tbl_cat
    where table1.id_animal = tbl_cat.id and
          table1.animal = 'cat';

For some inexplicable reason, the three major databases that support join in update clauses (MySQL, SQL Server, and Postgres) all have different syntax. Your syntax is the SQL Server syntax.

Upvotes: 1

Related Questions