Mysql update语句赋值嵌套select

Mysql update语句赋值嵌套select

 

Java代码  

update a set col=(select col from a where id='5') where id>5 and id<10;  

  www.2cto.com  

报错了 

ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause 

 

发现是  mysql 定义update语句不能同时对同一张进行set 赋值操作，也就是说 

update a 的时候 不能在后面select col from a ，如果是不同表操作是没有问题的。 

 

解决方法：

 

将select那里的a的表起一个别名b 就可以解决这个问题  

update a set col=(select col from (select * from a ) as b where id='5' )where id>5 and id <10;  

 $sql0 = 'UPDATE `d_productclass` SET path = CONCAT('-0-') where `level` = 0';
 $sql1 = 'UPDATE `d_productclass` SET path = CONCAT('-0-',pid,'-') where `level` = 1';
 $sql2 = 'UPDATE `d_productclass` SET path = CONCAT((SELECT path FROM (SELECT * FROM d_productclass) as b where b.id = d_productclass.pid),pid,'-') where `level` = 2';