解决mysql不能修改（You can't specify target table 'basic_data' for update in FROM clause）

今天遇到一个问题

You can't specify target table 'basic_data' for update in FROM clause

原句

UPDATE basic_data SET manage_user=4 WHERE id IN 
(SELECT b.id from basic_data b JOIN department d on d.id =b.department JOIN users u on u.department_id=d.id  where d.id=1) 

修改后

UPDATE basic_data SET manage_user=4 WHERE id IN 
(SELECT b.id from (SELECT * FROM basic_data) b JOIN department d on d.id =b.department JOIN users u on u.department_id=d.id  where d.id=1) 

原理 是mysql 把 查询子句当做一个临时表，不在当做一个实体表