尝试删除sql中的重复记录，但查询进入无限循环

this is my table test

这是我的表测试

id   identifier
---  ---------
1      zz
1      zzz
3      d 
5      w
7      v
8      q
9      cc
9      ccc

here I want to remove the duplicate id's and keep the latest id's. the identifier can have duplicate values it dose not matter but the id's should be unique.

在这里,我想删除重复的ID并保留最新的ID。标识符可以有重复的值,这不重要,但id应该是唯一的。

I wrote this query to solve this problem but the problem is that it goes into a infinite loop.

我写了这个查询来解决这个问题,但问题是它进入了无限循环。

please help me with this as I am not able to see the error. Thanks

请帮助我,因为我无法看到错误。谢谢

 delete test
    from test
    inner join(
    select max(id) as lastId, identifier
    from test
    where id in (
              select id 
                from test
               group by id
              having count(*) > 1
       )
    group by id
    )dup on dup.id = test.id
    where test.id<dup.id

4 个解决方案

#1

If you have an index on test(id, identifier), the following should be pretty efficient:

如果你有一个关于test(id,identifier)的索引,那么下面应该非常有效:

delete from test
    where test.identifer < (select maxid 
                            from (select max(identifier) as maxid from test t2 where t2.id = t.id
                                 ) a
                           )

The double nested query is a MySQL trick for referencing the update/delete table in the same query.

双嵌套查询是用于在同一查询中引用更新/删除表的MySQL技巧。

4 个解决方案

#1

更多相关文章

随机推荐