mysql 中 You can't specify target table for update in FROM clause 错误解决方法

时间:2021-09-01 22:59:41

You can’t specify target table tbl for update in FROM clause

在mysql中当在一句sql中先后执行对同一张表的select 和 update 或 delete 操作时,就会报错:
You can't specify target table tbl for update in FROM clause

例如:

update tbl as a set a.col = 
(
select b.col from tbl as b where b.id = 1
);

解决方法: 将select字句中的表再嵌套一层select

update tbl as a set a.col = 
(
select c.col from
(
select * from tbl as b where b.id = 1
)
as c
);