因为要更改一个表的一个字段的值,需要满足一定的条件更改,然后发现报错如下:
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);