更新SQL列，如果有重复的值

Question

我试图写一个查询来更新我的表，如果任何值出现不止一次。例子:

表:

复制假假假假

使用子查询检索重复值，然后使用JOIN将重复列更新为true

-- PostgreSQL (v11)
UPDATE test
SET duplicate = true
FROM (
SELECT name t_name
, COUNT(name) total
FROM test
GROUP BY name
HAVING COUNT(name) > 1
) t
WHERE name = t.t_name;

请从url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=92dfeb1b15d141e007fa49e61168e416查看

-- MySQL (5.6 to upper)
UPDATE test p
INNER JOIN (SELECT name t_name
, COUNT(name) total
FROM test
GROUP BY name
HAVING COUNT(name) > 1
) t
ON p.name = t.t_name
SET duplicate = true

请从url https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=d2571368bb55803276b4495cdff9a226查看