Best Workaround with LIMIT subquery MySQL
i want to create Stored PROCEDURE with multi statement, and it not working , and Google the problem and found that mysql dose not support Subquery statement "MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"
My statement like this:
DROP PROCEDURE IF EXISTS proc_Name;
CREATE PROCEDURE `DBName`开发者_如何学Go.`proc_Name`()
BEGIN
SELECT FROM table1 WHERE ORDER BY table1_Colom LIMIT 100;
UPDATE table2 SET table2_colom1 = 1 WHERE ID IN (SELECT ID FROM table2 ORDER BY table2_colom1 LIMIT 100);
END ;
UPDATE
table2, (SELECT ID FROM table2 ORDER BY table2_colom1 LIMIT 100) AS temp_table
SET
table2_colom1 = 1
WHERE
table2.ID = temp_table.id;
I have a sneaking (and optimistic) feeling that the issue here is just SELECTing on the same table you perform update on. The ORDER BY is dependent on the exact ID you are changing. I think alias miht help, so give different alias and try:
UPDATE table2 T2 SET T2.table2_colom1 = 1 WHERE T2.ID IN
(SELECT T2A.ID FROM table2 T2A ORDER BY T2A.table2_colom1 LIMIT 100);
I don't have the means to test now =(
Let me know if it works!
精彩评论