使用 HAVING in UPDATE with WHERE 子句

Question

>我正在尝试通过将列 V 设置为 2 来更新表，其中列图像中有重复值

SET 
VAT = 2
WHERE id >0  
HAVING count(image) > 1

Answer 1

从您的评论到之前的答案，我假设您使用MySql。
在 MySql 中，您需要将表联接到返回重复图像的查询：

update tablename t inner join (
select image   
from tablename
where id > 0
group by image
having count(*) > 1
) i on i.image = t.image
set vat = 2;

Answer 2

你可以使用它。

对于 SQL Server

update t set t.VAT = 2 from applicantinfo as t inner join
(select Image from applicantinfo group by image having count(*)>1) as b 
on t.image = b.image

Answer 3

你可以这样做：

UPDATE applicantinfo
SET VAT = 2
WHERE image IN (
SELECT image
FROM (SELECT * FROM applicantinfo)
WHERE id > 0
GROUP BY image
HAVING COUNT(*) > 1
)

WHERE子句中的SELECT提供重复的image行，ids 大于零。