mysql - 更新内部连接卡住

Question

出于某种原因，此 UPDATE 查询永远挂起。如果我替换为 SELECT - 它会立即得到结果。

UPDATE table1 AS t1
INNER JOIN
(
    SELECT count(*) as total, left(number,7) as prefix, outcome FROM table1
    where outcome like '%Passed%'
    group by prefix 
    order by total desc limit 200
) AS t2 
ON t2.prefix = left(t1.number,7) AND t1.outcome = 'Fail'
SET t1.outcome = '', t1.status = 'NEW'

哪里出了问题？

Answer 1

尝试将 ORDER BY 和 LIMIt 移动到 UPDATE 的末尾。像这样的东西:

UPDATE table1 AS t1
INNER JOIN
(
    SELECT count(*) as total, left(number,7) as prefix, outcome FROM table1
    where outcome like '%Passed%'
    group by prefix 
) AS t2 ON t2.prefix = left(t1.number, 7) AND t1.outcome = 'Fail'
SET t1.outcome = '', t1.status = 'NEW'
order by total desc 
limit 200;

请参阅 UPDATE 的语法.