似乎需要 GROUP BY 的 SQL 更新

Question

我想对表格进行更新，例如

UPDATE mytable
LEFT JOIN worker ON first=worker_first_name && last=worker_last_name
GROUP BY CONCAT(first, '%%', last)
SET taint = COUNT(pkey) > 1;

当然，UPDATE 中的 table_reference 不允许 GROUP BY。如何设置此列？它只是检查名字/姓氏组合是否在另一个表中最多出现一次。

Answer 1

UPDATE mytable
INNER JOIN (
  select first, last, COUNT(pkey) > 1 Result
  FROM mytable
  LEFT JOIN worker ON first=worker_first_name AND last=worker_last_name
  GROUP BY first, last) OTHER
    on OTHER.first = mytable.first
   and OTHER.last = mytable.last
SET taint = OTHER.Result;