update x set available_material_id = null where id not in (select id from x where additional_info = 1);
大致意思是,在同一语句中,不能先select出同一表中的某些值,再update这个表。
You can't specify target table 'x' for update in FROM clause
update x left join
x xx
on x.id = xx.id and xx.additional_info = 1
set available_material_id = null
where xx.id is null;
原始:
DELETE FROM tempA WHERE tid IN (
SELECT MAX(tid) AS tid FROM tempA GROUP BY name,age
)
改造后
DELETE FROM tempA WHERE tid NOT IN (
SELECT t.tid FROM (
SELECT MAX(tid) AS tid FROM tempA GROUP BY name,age
) t
)
查询的时候增加一层中间表,就可以避免该错误。
https://stackoverflow.com/questions/51087937/on-update-mysql-row-you-cant-specify-target-table-x-for-update-in-from-claus
https://blog.csdn.net/h996666/article/details/81699255
https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause/14302701
https://www.cnblogs.com/pcheng/p/4950383.html
https://blog.csdn.net/poetssociety/article/details/82391523
联系客服