翻译:MySQL不能指定更新的目标表在FROM子句
源SQL语句:
SQL> delete from t_official_sys_user where USER_NAME IN(SELECT USER_NAME FROM t_official_sys_user b group by b.`USER_NAME` having count(1) > 1)
SQL> delete from t_official_sys_user where USER_NAME IN(SELECT USER_NAME FROM t_official_sys_user b group by b.`USER_NAME` having count(1) > 1) [Err] 1093 - You can't specify target table 't_official_sys_user' for update in FROM clause
解决代码如下:
SQL>from t_official_sys_user where USER_NAME IN(select USER_NAME from ( SELECT USER_NAME FROM t_official_sys_user b group by b.`USER_NAME` having count(1) > 1) as temtable );
分析:
先把要删除的目标放到一张临时表再把要删除的条件指定到这张临时表即可。