Mysql 报错 “You can‘t specify target table ‘tablename‘ for update in FROM clause“ 解决方案

报错 “You can‘t specify target table ‘tablename‘ for update in FROM clause“ 解决方案"/>

Mysql 报错 “You can‘t specify target table ‘tablename‘ for update in FROM clause“ 解决方案

问题描述：

 执行SQL实现重复数据去重保留一条数据，报错 "You can't specify target table 'tablename' for update in FROM clause" 。

原报错SQL：

DELETE
FROMkng_user_study
WHEREid IN (SELECTb.idFROMkng_user_study bWHEREb_id = 'dc9de391-e4e9-4704-a881-ba7b74a37e57'AND b.kng_id IN (SELECTa.kng_idFROMkng_user_study aWHEREa_id = 'dc9de391-e4e9-4704-a881-ba7b74a37e57'GROUP BYa.user_id,a.kng_idHAVINGcount(*) > 1)AND b.user_id IN (SELECTa.user_idFROMkng_user_study aWHEREa_id = 'dc9de391-e4e9-4704-a881-ba7b74a37e57'GROUP BYa.user_id,a.kng_idHAVINGcount(*) > 1)AND b.id NOT IN (SELECTmin(a.id)FROMkng_user_study aWHEREa_id = 'dc9de391-e4e9-4704-a881-ba7b74a37e57'GROUP BYa.user_id,a.kng_idHAVINGcount(*) > 1));

修改后的SQL：

DELETE
FROMkng_user_study
WHEREid IN (SELECTd.idFROM(SELECTb.idFROMkng_user_study bWHEREb_id = 'dc9de391-e4e9-4704-a881-ba7b74a37e57'AND b.kng_id IN (SELECTa.kng_idFROMkng_user_study aWHEREa_id = 'dc9de391-e4e9-4704-a881-ba7b74a37e57'GROUP BYa.user_id,a.kng_idHAVINGcount(*) > 1)AND b.user_id IN (SELECTa.user_idFROMkng_user_study aWHEREa_id = 'dc9de391-e4e9-4704-a881-ba7b74a37e57'GROUP BYa.user_id,a.kng_idHAVINGcount(*) > 1)AND b.id NOT IN (SELECTmin(a.id)FROMkng_user_study aWHEREa_id = 'dc9de391-e4e9-4704-a881-ba7b74a37e57'GROUP BYa.user_id,a.kng_idHAVINGcount(*) > 1)) d);

结果分析：

把 select b.id from kng_user_study b where  ...的结果集再另存为新表，自查一遍，这样就独立出来不跟原kng_user_study表有关联，可解决此报错。同时这也是一个典型的MySQL重复数据去重保留一条的写法，大家可以参考下。