Mysql 批量更新 1093 – You can’t specify target table ‘questions’ for update in FROM clause

01. December 2017 DataBase 0

Mysql中更新和查询条件在同一个表中会出现1093 – You can’t specify target table ‘questions’ for update in FROM clause错误,错误代码如下:

update questions set number=3 where id in (SELECT id from questions WHERE choose LIKE "%C%" and
 choose NOT LIKE "%D%")

可通过如下方法解决:

UPDATE questions s set number=3 where s.id IN (SELECT t.id from (SELECT * FROM questions WHERE
 choose LIKE "%C%" and choose NOT LIKE "%D%") t)