mysql You can't specify target t

mysql 不能对同一个语句进行查询又更新

问题

• 涉及表（字段）

  
  涉及表字段.png

• 执行语句

UPDATE `TbBookStock`  SET nState = 2  WHERE pkCode IN ( SELECT pkCode FROM `TbBookStock` WHERE nState = 5 AND DATE_ADD(tOffShelf,INTERVAL '48' HOUR)  <= NOW() )

• 错误提示
  You can't specify target table 'TbBookStock' for update in FROM clause

解决

• 修改语句

UPDATE `TbBookStock`  SET nState = 2  WHERE pkCode IN ( SELECT pkCode FROM ( SELECT pkCode FROM `TbBookStock` WHERE nState = 5 AND DATE_ADD(tOffShelf,INTERVAL '48' HOUR)  <= NOW()) AS a )

• 执行结果

  
  更新成功.png

参考文档

• 官方文档地址