在线跪等，一条mysql的delete语句错误
表 t_test
id colm1 clom2
1 2 3
2 2 3
3 3 4
4 3 4
5 4 6
6 4 6
7 4 6
id为自增主键，用一条delete语句删除（colm1,colm2)的重复记录
delete from t_test where id not in
(
select max(id) from t_test group by column1,column2
);
我觉得这条语句好像没问题，可是错误提示
错误提示：You can’t specify target table ‘t_test’ for update in FROM clause
mysql> delete from t where id not in (select max(id) from t group by col2, col3)
;
ERROR 1093 (HY000): You can’t specify target table ‘t’ for update in FROM clause
mysql中不能这么用。
错误提示就是说，不能先select出同一表中的某些值，再update这个表(同一语句中)
替换方案：
mysql> create table t1 as select max(id) as col1 from t group by col2, col3;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t1;
+——-+———+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———+——+—–+———+——-+
| col1 | int(11) | YES | | NULL | |
+——-+———+——+—–+———+——-+
1 row in set (0.00 sec)
mysql> delete from t where id not in (select col1 from t1);
Query OK, 2 rows affected (0.11 sec)
這樣吧,你先産生一個視圖
create view v_test as select max(id) as id from t_test group by colm1,colm2;
然後再執行刪除語句:
delete from t_test where id not in (select id from v_test);
