Mysql如何对同一个表进行update(insert)和select操作

[csiamgo1]

如图，具体语句如下，大家帮忙改下，原来的是SQL Server的语句，现在要改成Mysql的

update Type set Type_Name='Communication', Type_Desc='Phase: Requirement', Parent_Id=(select ID from Type where Type_Name='Requirement' and Parent_Id=0), Order_Number=1 where ID=1;

insert into Type 
select 'Review','Phase: Requirement',ID,3 from Type where Type_Name='Requirement' and Parent_Id=0;

[ACMAIN_CHM]

MYSQL这个比较特殊，一般是通过 表的自连接来实现，也就是你使用的这种方法。

[wwwwb]

SQL语句有问题？提示什么

[csiamgo1]

You can't specify target table 'Type' for update in FROM clause
网上说是不能在update的时候在同一个表做select操作

[小小小小周]

你就当是2张表,自连接来更新

[csiamgo1]

已经自己解决了，附上代码

update Type a,(select ID from Type where Type_Name='Requirement' and Parent_Id=0) b set a.Type_Name='Communication', a.Type_Desc='Phase: Requirement', a.Parent_Id=b.id, a.Order_Number=1 where a.ID=1;

insert into Type(Type_Name,Type_Desc,Parent_Id,Order_Number) 
select 'Review','Phase: Requirement',ID,3 from Type where Type_Name='Requirement' and Parent_Id=0;

[小小小小周]

[wwwwb]

就是自连接查询
update Type a,type b
set a.Type_Name='Communication', a.Type_Desc='Phase: Requirement', a.Parent_Id=b.id, a.Order_Number=1 where a.ID=1 and b.Type_Name='Requirement' and b.Parent_Id=0;

[老骥伏枥]

来学习的，看来就是用mysql的自连接来解决。