mysql - 如何更新同一个表上具有不同值的多个重复项？

Question

我正在处理的表有多行，其中 lat 和 lon 具有相同的值。该示例显示 1、3、5 具有相同的位置，但 name 属性不同。 哈希是根据name、lat和lon构建的，因此有所不同。

BEFORE:

 id | name | lat | lon | flag | hash
----+------+-----+-----+------+------
 1  | aaa  | 16  | 48  |  0   | 2cd       <-- duplicate
 2  | bbb  | 10  | 22  |  0   | 3fc
 3  | ccc  | 16  | 48  |  0   | 8ba       <-- duplicate
 4  | ddd  | 10  | 23  |  0   | c33
 5  | eee  | 16  | 48  |  0   | 751       <-- duplicate

我需要识别此表中的“重复项”，并希望将标志 1(primary)分配给其中一个，并将标志 2(次要)给其他人。哪个“重复”被标记为主要并不重要。

 AFTER:

 id | name | lat | lon | flag | hash
----+------+-----+-----+------+------
 1  | aaa  | 16  | 48  |  1   | 2cd       <-- updated
 2  | bbb  | 10  | 22  |  0   | 3fc
 3  | ccc  | 16  | 48  |  2   | 8ba       <-- updated
 4  | ddd  | 10  | 23  |  0   | c33
 5  | eee  | 16  | 48  |  2   | 751       <-- updated

我开始尝试INNER JOIN inspired by this post还有这个visual description 。这样我就可以为所有重复项分配相同的标志。

UPDATE table t1
INNER JOIN table_name t2
ON
  t1.lat = t2.lat
  AND t1.lon = t2.lon
  AND t1.hash != t2.hash
SET
  t1.flag = 2;

我还使用 WHERE t2.id IS NULL 测试了 LEFT OUTER JOIN ，当只有两行时它可以工作。但是，我无法思考 JOIN 应该如何处理两个以上的重复项。 Mark Harrison 还假设“您正在加入没有重复项的列”at the beginning of his post听起来这似乎不是一个好主意。

如果对此感兴趣，我正在使用 MySQL。

Answer 1

不确定这是否非常有效，但它works in just one query :

UPDATE t
JOIN (
  SELECT MAX(t.id) AS maxid, lat, lon
  FROM t
  JOIN t AS duplicates
  USING (lat, lon)
  GROUP BY lat, lon
  HAVING COUNT(*) > 1
) AS maxima USING (lat, lon)
SET flag = IF(id = maxid, 1, 2);