mysql - 如何扩展和循环一组

Question

以下 SQL 命令列出了可以在多个对象上找到的哈希值。

SELECT *  FROM (
  SELECT 
    MIN(id) AS id, 
    hash, 
    status, 
    count(*) AS count 
  FROM foobar 
  GROUP BY hash 
  ORDER BY count
) AS t 
WHERE count > 1;

...

+------+----------------------------------+--------+-------+
|   id | hash                             | status | count |
+------+----------------------------------+--------+-------+
| 4523 | e4266978b1d99dffbf3a6e0b880a2c5e |      0 |     3 |
|  828 | 9414c7478416b7a40846d66e12df9370 |      0 |     4 |
|  293 | bfc742499fd97c4c8e36f57cdd0fa0e0 |      0 |     5 |
|  244 | ec408e4678789f7983f83a9c330ab8e4 |      0 |    14 |
+------+----------------------------------+--------+-------+
4 rows in set (0.02 sec)

我想更新组中每个项目的状态。对于单个组，可以按如下方式完成:

UDPATE foobar SET status = 9 WHERE hash = "e4266978b1d99dffbf3a6e0b880a2c5e";

如何更新每 26 个单独项目的状态？该解决方案至少应在 MySQL 上运行。

Answer 1

你可以使用

UPDATE foobar
SET status = 9 
WHERE hash IN (
  SELECT hash FROM (
    SELECT 
      hash, 
      count(*) AS count 
    FROM foobar 
    GROUP BY hash 
  ) AS t 
  WHERE count > 1
)

它将在 MySQL 中工作，因为 MySQL 将为使用 GROUP BY 的选择创建一个临时表。