Geek Answers Handbook

Freeze Internal Registration Update

For some reason, this UPDATE query hangs forever. If I replace with SELECT , it instantly gets the result.

 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?

+4
source share
3 answers

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 )

See the fiddle here .

+1
source

Try moving ORDER BY and LIMIt to the end of UPDATE . Sort of:

 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 UPDATE syntax.

+2
source

Can you update the column you are connecting to? That is t1.outcome .

Move filter expression t1.outcome = 'Fail' from JOIN and to to 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'
0
source

More articles:


All Articles