MySql查询选择然后删除 [英] MySql Query Select Then Delete

问题描述

我使用了一个 mysql 查询来选择我在数据库中的重复条目.

I have used a mysql query to select my duplicate entries in Database.

select id,DATE_FORMAT(cu_date,'%Y-%m-%d %T'),trunk_id ,count(*) from circuit_u
WHERE DATE_FORMAT(cu_date,'%Y-%m-%d') = '2013-01-26'
group by DATE_FORMAT(cu_date,'%Y-%m-%d %T'),trunk_id
HAVING count(*) > 1;

输出:

16347   2013-01-26 01:00:00 0   2
16372   2013-01-26 01:00:00 1   2
16397   2013-01-26 01:00:00 100 2
16422   2013-01-26 01:00:00 101 2
16447   2013-01-26 01:00:00 121 2
16472   2013-01-26 01:00:00 211 2
16497   2013-01-26 01:00:00 221 2
16522   2013-01-26 01:00:00 311 2

现在我想添加另一个命令来删除它们.

Now I want to add another command to delete them.

我尝试了很多方法，但每次都失败了.

I have tried with so many way but every time i failed.

请帮忙.

推荐答案

你可以试试这个

   DELETE from circuit_u WHERE id IN ( select * from (
   select id from circuit_u
   WHERE DATE_FORMAT(cu_date,'%Y-%m-%d') = '2013-01-26'
   group by DATE_FORMAT(cu_date,'%Y-%m-%d %T'),trunk_id
   HAVING count(*) > 1 ) t
   )

这篇关于MySql查询选择然后删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！