使用条件更新另一列的列(update column from another column with conditional)

使用条件更新另一列的列(update column from another column with conditional)

我有table1和tbl_cat 。 我想更新animal是cat的x值

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

结果期望：

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

我使用这个查询，但它不起作用：

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'

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'

最满意答案

Postgres中的正确语法是：

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

出于某种莫名其妙的原因，支持join更新子句（MySQL，SQL Server和Postgres）的三个主要数据库都具有不同的语法。 您的语法是SQL Server语法。

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.