如何更新仅显示一次的列中的值?

Question

如何仅更新那些在项目列中仅出现一次的项目的 id 值？

表1

id | item | price
-------------
10 | pen  | 10  
20 | pen  | 10   
30 | pen  | 10
30 | copy | 10
10 | book | 10
10 | ball | 10

Answer 1

update table set 
id = <whatever>
where
item in (select item from table group by item having count(*)=1)

Answer 2

您可以使用not exists：

update t 
set t.id = <whatever>
where not exists (select 1 from table where item = t.item and id <> t.id);

Answer 3

试试这个

Update Table1 set id = "val" 
group by item
having count(*) = 0