在Postgresql中，用列值上的where子句更新表

Question

我有一个类似的表名table

| label_id| label_name | user_id|
----------------------------------
|    1    | insvt1     |   1    |
|    2    | invest2    |   1    |
|    3    | invest3    |   1    |
|    4    | ivsest3    |   2    |
|    5    | invest4    |   3    |

如果标签名称与row 3 and row 4 then no update will occur相同，则label_name不相同，我想更新user_id 1,1,1 with user_id 2的user_id列

更新后的结果应该是这样的

| label_id| label_name | user_id|
----------------------------------
|    1    | insvt1     |   2    |
|    2    | invest2    |   2    |
|    3    | invest3    |   1    |
|    4    | ivsest3    |   2    |
|    5    | invest4    |   3    |

我试过这个

UPDATE data_table t, data_table t1
SET t.user_id = 2
WHERE t.label_name <> t1.label_name (!= this also)
AND (t.user_id = 1 or t1.user_id=2)

它在更新，但也在更新lebel_name和相同的地方

如有任何帮助，将不胜感激

Answer 1

您的代码看起来像MySQL。这建议使用JOIN作为逻辑：

UPDATE data_table t JOIN
(SELECT label_name, COUNT(*) as cnt
FROM data_table t1
GROUP BY label_name
HAVING COUNT(*) = 1
) tt
ON t.label_name = tt.label_name
SET t.user_id = 2
WHERE t.user_id = 1;

在Postgres中，这看起来像：

UPDATE data_table t 
SET t.user_id = 2
FROM (SELECT label_name, COUNT(*) as cnt
FROM data_table t1
GROUP BY label_name
HAVING COUNT(*) = 1
) tt
WHERE t.label_name = tt.label_name AND t.user_id = 1;

Answer 2

下面的代码应该可以做到：

update data_table
SET [user_id] = 2
WHERE label_name IN ( SELECT label_name FROM data_table GROUP BY label_name HAVING COUNT(*) = 1) AND [user_id] = 1

说明：您将更新数据表，但仅更新那些标签描述只出现一次的行。所以我在标签_ name in(….(中写的标准