在学习时,我在update语句中子查询了修改表,报出如下错误
You can‘t specify target table ‘beauty‘ for update in FROM clause
原因是不能子查询select出同一表中的某些值,再update这个表(在同一语句中)
错误案例
UPDATE beauty SET boyfriend_id =2
WHERE beauty.id IN (SELECT beauty.id id FROM beauty LEFT JOIN boys ON boys.id=beauty.`boyfriend_id` WHERE beauty.`boyfriend_id` IS NULL)
应该写法
UPDATE beauty SET boyfriend_id =2 WHERE beauty.id
IN (SELECT b.id FROM
(SELECT beauty.id id FROM beauty LEFT JOIN boys ON boys.id=beauty.`boyfriend_id`
WHERE beauty.`boyfriend_id` IS NULL)
AS b)
虽然其实可以直接外连接修改。但是误打误撞这个问题,记录一下