sql陳述句(update/delete都會出現此問題)
update x set available_material_id = null where id not in (select id from x where additional_info = 1);
mistake
大致意思是，在同一陳述句中，不能先select出同一表中的某些值，再update這個表，
You can't specify target table 'x' for update in FROM clause
mysql5.7解決辦法
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;
老辦法(有人說5.7已經不能用了)
原始:
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/82527.html
標籤：MySQL
上一篇：關于Oracle用java實時監聽oracle對表的DML操作
下一篇：com.mysql.jdbc.Driver 和 com.mysql.cj.jdbc.Driver的區別 serverTimezone設定