从表中删除重复的记录-SQL查询[英] Deleting duplicate record from table - SQL query

本文是小编为大家收集整理的关于从表中删除重复的记录-SQL查询的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我只需要从表中删除重复行,就像我在表中有3个重复行一样,我的查询将从3个重复的行中删除2行.

我该如何得到这个?请帮助我.

推荐答案

请尝试以下查询,它肯定会符合您的目标

SET ROWCOUNT 1
DELETE test
FROM test a
WHERE (SELECT COUNT(*) FROM test b WHERE b.name = a.name) > 1
WHILE @@rowcount > 0
  DELETE test
  FROM test a
  WHERE (SELECT COUNT(*) FROM test b WHERE b.name = a.name) > 1
SET ROWCOUNT 0

其中测试是您的表名称

其他推荐答案

这在SQL Server中起作用,尽管它不是一个单一的语句:

Declare @cnt int; 
Select @cnt=COUNT(*) From DupTable Where (Col1=1);  -- Assumes you are trying to delete the duplicates where some condition (e.g. Col1=1) is true.
Delete Top (@cnt-1) From DupTable

它也不需要任何 extrape 假设(例如,另一行独特的另一列的存在).毕竟,桑塔努确实说行是重复的,而不仅仅是一列.

但是,我认为右>答案是获得真实的表结构.也就是说,在此表中添加一个身份列,以便您可以使用单个SQL命令来完成您的工作.这样:

ALTER TABLE dbo.DupTable ADD
    IDCol int NOT NULL IDENTITY (1, 1)
GO

然后删除是微不足道的:

DELETE FROM DupTable WHERE IDCol NOT IN 
   (SELECT MAX(IDCol) FROM DupTable GROUP BY Col1, Col2, Col3)

其他推荐答案

DELETE FROM Table t1, Table t2 WHERE t1.colDup = t2.colDup AND t1.date < t2.date

将从Table(在列colDup上)删除每个重复行,除了最古老的行(即Lowset date).

本文地址:https://www.itbaoku.cn/post/597623.html

问题描述

I need to delete duplicate rows only from the table, like I have 3 duplicate rows in the table, my query will delete 2 rows from 3 duplicated rows.

How can I get this? Please help me.

推荐答案

Please try the below query, it will definitely meet your objective

SET ROWCOUNT 1
DELETE test
FROM test a
WHERE (SELECT COUNT(*) FROM test b WHERE b.name = a.name) > 1
WHILE @@rowcount > 0
  DELETE test
  FROM test a
  WHERE (SELECT COUNT(*) FROM test b WHERE b.name = a.name) > 1
SET ROWCOUNT 0

where test is your table name

其他推荐答案

This works in SQL Server although it isn't a single statement:

Declare @cnt int; 
Select @cnt=COUNT(*) From DupTable Where (Col1=1);  -- Assumes you are trying to delete the duplicates where some condition (e.g. Col1=1) is true.
Delete Top (@cnt-1) From DupTable

It also doesn't require any extra assumptions (like the existance of another column that makes each row unique). After all, Santanu did say that the rows were duplicates and not just the one column.

However, the right answer, in my view, is to get a real table structure. That is, add an IDENTITY column to this table so that you can use a single SQL command to do your work. Like this:

ALTER TABLE dbo.DupTable ADD
    IDCol int NOT NULL IDENTITY (1, 1)
GO

Then the delete is trivial:

DELETE FROM DupTable WHERE IDCol NOT IN 
   (SELECT MAX(IDCol) FROM DupTable GROUP BY Col1, Col2, Col3)

其他推荐答案

DELETE FROM Table t1, Table t2 WHERE t1.colDup = t2.colDup AND t1.date < t2.date

Will delete every duplicate row from Table (on column colDup) except the oldest (i.e. lowset date).