更新内部连接冻结

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的语法

Answer 2

我会尝试这样做:

UPDATE table1
SET
  table1.outcome = '',
  table1.status = 'NEW'
WHERE
  outcome = 'Fail'
  AND 
  left(number,7) IN (SELECT * FROM (
    SELECT left(number,7) as prefix
    FROM table1
    WHERE outcome like '%Passed%'
    GROUP BY prefix 
    ORDER BY COUNT(*) desc limit 200
    ) s
  )

Answer 3

您可以更新您正在加入的专栏吗?即t1.outcome。

将过滤器表达式t1.outcome = 'Fail'从JOIN中移出到WHERE子句中。

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)
SET t1.outcome = '', t1.status = 'NEW'
WHERE t1.outcome = 'Fail'