CODEWITHSUNDEEP
mysqlsql
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Update Inner Join Freeze

For some reason, this UPDATE query hang forever. If I replace to SELECT - it get the result instantly.

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'

What is wrong there?

Upvotes: 4

Views: 252

Answers (3)

fthiella
fthiella

Reputation: 49049

I would try something like this:

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
  )

Please see fiddle here.

Upvotes: 1

Sepster
Sepster

Reputation: 4849

Can you update the column on which you're joining? That is, t1.outcome.

Move the filter expression t1.outcome = 'Fail' out of the JOIN and in to a WHERE clause. 

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'

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try to move the ORDER BY and LIMIt to the end of the UPDATE. Something like:

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;

See the syntax of the UPDATE.

Upvotes: 2

Related Questions