开发者

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!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜