MySQL计算列中的值，更新和替换有限制的值

Question

我的 MySQL 更新语句有问题。我的表中有超过 100.000 个条目。所以我的方法并不成功。 (见下文)

首先，我想计算值的数量(这很简单):

SELECT values1 ,count(values1) FROM table
GROUP BY value1
HAVING COUNT(value1) <= 1000;

其次，我想替换列values1中仅出现<= 1000次的一些值。

所以我尝试了这些语句:

Update table as t
SET t.value1 = "limitAmount" 
WHERE EXISTS (select value1 from 
(select * from table) as f Group by f.value1
Having count(f.value1) <= 1000);

当我尝试这个 SQL 语句时，我收到:

Error Code 1205. Lock Wait timeout exceeded. Try restarting transaction.

Answer 1

试试这个

Update 
  table as t 
SET 
  t.value1 = "limitAmount" 
WHERE 
  EXISTS (select value1 from (select * from table) as f where f.value1 = t.value1 Group by f.value1 Having count(f.value1) <= 1000);

注意子查询中的where f.value1 = t.value1条件