SQL删除表中所有除id不同外其他字段都相同的重复记录
表结构如下:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`co1` int(11) NOT NULL,
`co2` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表记录如下:
| id co1 co2|
| 1 2 3 |
| 2 4 5 |
| 3 4 5 |
因为后两条记录除id外其他字段都相同,所以要删除其中一条。
删除重复记录,保留重复记录中的最小id的SQL语句:
delete from test where id not in
(select * from ((select min(id) from test group by co1,co2)) tmp);
解释:
select min(id) from test group by co1,co2
可以找到 (1, 2, 3) 和 (2, 4, 5) 两条记录。delete from test where id not in (select min(id) from test group by co1,co2)
先找到id不在上述两条记录的记录,即 (3, 4, 5),然后把 (3, 4, 5) 删除,那么剩下的自然就是 (1, 2, 3) 和 (2, 4, 5) 两条记录了。- 但是在执行2中SQL语句时,报错:ERROR 1093 (HY000): You can’t specify target table ‘test’ for update in FROM clause,这是因为不能对进行查询操作的表进行update操作,也就说我们的where条件中进行了子查询,并且子查询也是针对需要进行update操作的表的,mysql不支持这种查询修改的方式。因此,针对这个问题,可以通过”绕”的方式进行实现,即采取
主句(select * from (从句 temp))
的形式即可。因此对2中的SQL语句进行这种修改就是最终的答案。