mysql中You can't specify target table for update in FROM clause

標籤：

使用mysql在刪除表中重複記錄

delete from user where username in (select user name form(select username from user group by username having count(username)>1));

遇到mysql報錯You can‘t specify target table for update in FROM clause

上網百度了下原來是mysql中不允許先select出同一表中的某些值，再update這個表(在同一語句中)，這個問題只出現於mysql，sqlserver和oracle不會出現此問題

解決辦法：將select出的結果再通過中間表select一遍

delete from user where username in (select a.username from(select username from user group by username having count(username)>1)a);

這樣delete和select的就不是同一個表了

mysql中You can't specify target table for update in FROM clause