Mysql出错之 You can't specify target table 'commodity' for update in FROM clause（不可以先select某些值在进行更新操作）

错误代码：将commodity表中的INVENTORY值减小10（应用案例 点餐系统“10”为出库产品数量） update commodity set INVENTORY=(select INVENTORY from commodity where S_ID=1001)-10 where S_ID=1001

修正代码：先操作数据，在将操作后的表作为一张虚表查询
public int findAll2(int sId,int count) {
String sql ="UPDATE commodity set INVENTORY=(select a.INVENTORY from (select INVENTORY-"+count+" AS INVENTORY from commodity where S_ID="+sId+") a) where S_ID="+1001+";";
int i = super.executeUpdate(sql, null);