【mysql】You can't specify target table for update in FROM clause 报错原因以及解决

因为要更改一个表的一个字段的值，需要满足一定的条件更改，然后发现报错如下：

sql语句如下

update g_stock_info set special_stock_status = '1' where uid in (select gs.g_stock_info_uid as sss from g_stock_info_detail gs left join g_stock_info gsi on gs.`g_stock_info_uid` = gsi.uid where gs.type = '2' and gs.flag = '1' and gs.`g_stock_info_uid` is not null group by gs.`g_stock_info_uid`);

之所以报错是因为MySQL 修改或者删除数据时候不能以当前表作为 from 条件，但是oracle是可以的。

如果有这种需求的话，需要建立一个临时表，sql语句如下

update g_stock_info set special_stock_status = '1' where uid in (select sss from 
(select gs.g_stock_info_uid as sss from g_stock_info_detail gs left join g_stock_info gsi on gs.`g_stock_info_uid` = gsi.uid where gs.type = '2' and gs.flag = '1' and gs.`g_stock_info_uid` is not null group by gs.`g_stock_info_uid`)as ss);