当mysql报错:1093 - You can’t specify target table ‘department’ for update in FROM clause 的时候,说明你想查询一张表的数据,再根据这个数据修改同一张表的数据,这在mysql是不允许的。但是有其他方法可以实现同样的效果:
原来的sql:
UPDATE department
SET isParent = 0
WHERE
id = ( SELECT parentId FROM department WHERE id = 108 )
修改后:
UPDATE department
SET isParent = 1
WHERE id =
(
SELECT d2.parentId
FROM (
SELECT d3.parentId
FROM department as d3
WHERE d3.id = 108
) as d2
);
显而易见,就是再用一层select语句包起来,相当于把查询数据的select修饰成一个表,这样就可以"骗"过mysql。