Comment puis-je supprimer les lignes dupliquées?

Quelle est la meilleure façon de supprimer les lignes dupliquées d'un tableau assez grand SQL Server (c.-à-d. 300 000+ lignes)?

les rangées, bien sûr, ne seront pas des doublons parfaits en raison de l'existence du champ d'identité RowID .

Matable

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
1170
demandé sur DineshDB 2008-08-21 01:51:29

30 réponses

en supposant aucun nulls, vous GROUP BY les colonnes uniques, et SELECT le MIN (or MAX) RowId comme la rangée à garder. Ensuite, il suffit de supprimer tout ce qui n'avait pas de numéro d'identification de ligne:

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

dans le cas où vous avez une GUID au lieu d'un entier, vous pouvez remplacer

MIN(RowId)

avec

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))
1072
répondu Mark Brackett 2017-06-12 11:06:07

une autre façon possible de faire ceci est

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

j'utilise ORDER BY (SELECT 0) ci-dessus car il est arbitraire quelle ligne conserver en cas d'égalité.

pour préserver le dernier dans RowID commande par exemple, vous pouvez utiliser ORDER BY RowID DESC

Plans D'Exécution

Le plan d'exécution est souvent plus simple et plus efficace que dans l'acceptation réponse car il ne nécessite pas l'auto-jointure.

Execution Plans

ce n'est toutefois pas toujours le cas. Un endroit où la solution GROUP BY pourrait être préférée est celui où un agrégat de hachage serait préféré à un agrégat de flux.

la solution ROW_NUMBER donnera toujours à peu près le même plan tandis que le GROUP BY la stratégie est plus souple.

Execution Plans

les facteurs qui pourraient favoriser l'approche agrégée de hachage seraient

  • Pas utile index sur les colonnes de partitionnement
  • groupes relativement moins nombreux avec relativement plus de doublons dans chaque groupe

dans les versions extrêmes de ce second cas (s'il y a très peu de groupes avec beaucoup de doubles dans chaque) On pourrait également envisager simplement insérer les lignes pour garder dans une nouvelle table puis TRUNCATE -ing l'original et les copier de nouveau pour minimiser la journalisation par rapport à la suppression d'une proportion très élevée des lignes.

709
répondu Martin Smith 2016-08-07 18:32:45

il y a un bon article sur suppression de doublons sur le site de soutien de Microsoft. C'est assez conservateur - ils vous font tout faire par étapes séparées - mais cela devrait bien fonctionner contre les grandes tables.

j'ai utilisé l'auto-jointures pour faire cela dans le passé, bien qu'il pourrait probablement être pré-lié avec une clause avoir:

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField
135
répondu Jon Galloway 2017-06-19 23:36:49

la requête suivante est utile pour supprimer les lignes dupliquées. Le tableau de cet exemple a ID comme colonne d'identité et les colonnes qui ont des données en double sont Column1 , Column2 et Column3 .

DELETE FROM TableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM   TableName
                  GROUP  BY Column1,
                            Column2,
                            Column3
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL) 

le script suivant montre l'utilisation de GROUP BY , HAVING , ORDER BY dans une requête, et renvoie les résultats avec la colonne dupliquer et son compte.

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC 
87
répondu gngolakia 2013-01-04 16:41:25
delete t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid

Postgres:

delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid
54
répondu SoftwareGeek 2015-11-07 00:48:15
DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 1 
41
répondu Jithin Shaji 2014-10-15 11:08:49

supprimer les lignes dupliquées, sauf la première ligne

DELETE
FROM
    Mytable
WHERE
    RowID NOT IN (
        SELECT
            MIN(RowID)
        FROM
            Mytable
        GROUP BY
            Col1,
            Col2,
            Col3
    )

Refer ( ) http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server )

36
répondu Syed Mohamed 2017-03-17 10:20:15

je préférerais CTE pour supprimer les lignes dupliquées de la table de serveur sql

fortement recommandé de suivre cet article:: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server /

en conservant l'original

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

sans conserver l'original

WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
 
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)
26
répondu Shamseer K 2018-08-12 03:31:33

rapide et sale pour supprimer les lignes identiques (pour les petites tables):

select  distinct * into t2 from t1;
delete from t1;
insert into t1 select *  from t2;
drop table t2;
21
répondu JuanJo 2013-02-05 21:44:52

je préfère la solution subquery\having count(*) > 1 à la jointure interne parce que je l'ai trouvé plus facile à lire et il était très facile de se transformer en une instruction SELECT pour vérifier ce qui serait supprimé avant de l'exécuter.

--DELETE FROM table1 
--WHERE id IN ( 
     SELECT MIN(id) FROM table1 
     GROUP BY col1, col2, col3 
     -- could add a WHERE clause here to further filter
     HAVING count(*) > 1
--)
17
répondu James Errico 2014-03-01 07:40:18
SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable
15
répondu heta77 2012-10-10 11:21:53

Pour Récupérer Les Lignes Dupliquées:

SELECT
name, email, COUNT(*)
FROM 
users
GROUP BY
name, email
HAVING COUNT(*) > 1

supprimer les lignes en double:

DELETE users 
WHERE rowid NOT IN 
SELECT MIN(rowid)
FROM users
GROUP BY name, email);
15
répondu Shaini Sinha 2018-01-17 07:03:50

encore une autre solution facile peut être trouvée au lien collé ici . Un facile à saisir et semble être efficace pour la plupart des problèmes similaires. C'est pour SQL Server mais le concept utilisé est plus qu'acceptable.

Voici les parties pertinentes de la page liée:

tenir compte de ces données:

EMPLOYEE_ID ATTENDANCE_DATE
A001    2011-01-01
A001    2011-01-01
A002    2011-01-01
A002    2011-01-01
A002    2011-01-01
A003    2011-01-01

alors comment pouvons-nous supprimer ces données dupliquées?

tout d'abord, insérer une colonne d'identification dans ce tableau en utilisant le code suivant:

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

utilisez le code suivant pour le résoudre:

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 
13
répondu Nitish Pareek 2013-11-05 21:39:20

j'ai pensé partager ma solution car elle fonctionne dans des circonstances spéciales. Dans mon cas, la table avec les valeurs dupliquées n'avait pas de clé étrangère (parce que les valeurs étaient dupliquées à partir d'une autre db).

begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2

-- insert distinct values into temp
insert into #temp 
select distinct * 
from  tableName

-- delete from source
delete from tableName 

-- insert into source from temp
insert into tableName 
select * 
from #temp

rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!

PS: quand je travaille sur des choses comme celle-ci, j'utilise toujours une transaction, ce qui permet non seulement de s'assurer que tout est exécuté dans son ensemble, mais aussi de tester sans rien risquer. Mais bien sûr, vous devriez faire une sauvegarde de toute façon juste pour être sûr...

13
répondu Ruben Verschueren 2014-01-27 12:20:09

cette requête a montré de très bonnes performances pour moi:

DELETE tbl
FROM
    MyTable tbl
WHERE
    EXISTS (
        SELECT
            *
        FROM
            MyTable tbl2
        WHERE
            tbl2.SameValue = tbl.SameValue
        AND tbl.IdUniqueValue < tbl2.IdUniqueValue
    )

il a supprimé 1m lignes dans un peu plus de 30sec d'un tableau de 2M (50% duplicata)

13
répondu Draško 2017-03-17 07:25:57

utilisant CTE. L'idée est de joindre sur une ou plusieurs colonnes qui forment un enregistrement dupliqué et puis supprimer ce que vous aimez:

;with cte as (
    select 
        min(PrimaryKey) as PrimaryKey
        UniqueColumn1,
        UniqueColumn2
    from dbo.DuplicatesTable 
    group by
        UniqueColumn1, UniqueColumn1
    having count(*) > 1
)
delete d
from dbo.DuplicatesTable d 
inner join cte on 
    d.PrimaryKey > cte.PrimaryKey and
    d.UniqueColumn1 = cte.UniqueColumn1 and 
    d.UniqueColumn2 = cte.UniqueColumn2;
13
répondu ostati 2018-02-12 15:12:24

voici un autre bon article sur suppression des doublons .

il discute pourquoi son dur: " SQL est basé sur l'algèbre relationnelle, et les doublons ne peuvent pas se produire dans l'algèbre relationnelle, parce que les doublons ne sont pas autorisés dans un ensemble. "

La table temp solution, et deux mysql exemples.

à l'avenir allez-vous l'empêcher au niveau d'une base de données, ou à partir d'une application perspective. Je vous suggère le niveau de base de données parce que votre base de données devrait être responsable du maintien de l'intégrité référentielle, les développeurs vont causer des problèmes ;)

12
répondu Craig 2008-08-20 21:58:00

bien sûr. Utilisez une table temporaire. Si vous voulez un simple, pas-très-performant énoncé que "fonctionne", vous pouvez aller avec:

DELETE FROM MyTable WHERE NOT RowID IN
    (SELECT 
        (SELECT TOP 1 RowID FROM MyTable mt2 
        WHERE mt2.Col1 = mt.Col1 
        AND mt2.Col2 = mt.Col2 
        AND mt2.Col3 = mt.Col3) 
    FROM MyTable mt)

fondamentalement, pour chaque ligne dans le tableau, la sous-sélection trouve le premier RowID de toutes les lignes qui sont exactement comme la ligne considérée. Ainsi, vous finissez avec une liste de RowIDs qui représentent les lignes "originales" non dupliquées.

12
répondu Jacob Proffitt 2013-06-10 19:28:05

j'avais une table où je devais préserver les lignes non-dupliquées. Je ne suis pas sûr de la vitesse ou de l'efficacité.

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )
11
répondu chrismar035 2009-12-11 13:47:21

l'autre voie est créer une nouvelle table avec les mêmes champs et avec L'Index Unique . Puis déplacer toutes les données de l'ancienne table à la nouvelle table . Ignorer automatiquement le serveur SQL (il y a aussi une option sur ce qu'il faut faire s'il y a une valeur dupliquée: ignorer, interrompre ou sth) les valeurs dupliquées. Nous avons donc la même table, sans doublons. si vous ne voulez pas D'Index Unique, après les données de transfert vous pouvez pose .

en particulier pour les tableaux plus grands vous pouvez utiliser DTS (paquet SSIS pour importer/exporter des données) afin de transférer toutes les données rapidement à votre nouvelle table indexée unique. Pour 7 millions de rang, ça ne prend que quelques minutes.

10
répondu İsmail Yavuz 2014-01-27 15:57:32

utiliser cette

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
   As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1
10
répondu Haris 2015-07-23 11:42:20

en utilisant la requête ci-dessous, nous pouvons supprimer les enregistrements en double basés sur la colonne simple ou la colonne multiple. ci-dessous la requête est la suppression basée sur deux colonnes. le nom de la table est: testing et les noms des colonnes empno,empname

DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
9
répondu Sudhakar NV 2012-03-09 15:50:38
  1. "créer un nouveau tableau blanc avec la même structure

  2. "
  3. Exécuter la requête comme ceci

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) > 1
    
  4. puis exécutez cette requête

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) = 1
    
9
répondu shA.t 2015-07-23 12:43:48

C'est la façon la plus facile de supprimer un enregistrement en double

 DELETE FROM tblemp WHERE id IN 
 (
  SELECT MIN(id) FROM tblemp
   GROUP BY  title HAVING COUNT(id)>1
 )

http://askme.indianyouth.info/details/how-to-dumplicate-record-from-table-in-using-sql-105

9
répondu Harikesh Yadav 2017-08-30 10:36:52

je mentionnerais cette approche aussi bien qu'elle peut être utile, et fonctionne dans tous les serveurs SQL: Assez souvent, il n'est que deux doublons: les Id et le nombre de doublons sont connus. Dans ce cas:

SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0
7
répondu Evgueny Sedov 2013-01-30 19:45:37

du niveau application (malheureusement). Je suis d'accord que la bonne façon d'empêcher la duplication est au niveau de la base de données grâce à l'utilisation d'un index unique, mais dans SQL Server 2005, un index est autorisé à être seulement 900 octets, et mon champ varchar(2048) souffle que loin.

Je ne sais pas comment il fonctionnerait, mais je pense que vous pourriez écrire un déclencheur pour l'appliquer, même si vous ne pouviez pas le faire directement avec un index. Quelque chose comme:

-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism 
ON stories 
after INSERT, UPDATE 
AS 
    DECLARE @cnt AS INT 

    SELECT @cnt = Count(*) 
    FROM   stories 
           INNER JOIN inserted 
                   ON ( stories.story = inserted.story 
                        AND stories.story_id != inserted.story_id ) 

    IF @cnt > 0 
      BEGIN 
          RAISERROR('plagiarism detected',16,1) 

          ROLLBACK TRANSACTION 
      END 

aussi, varchar (2048) sonne louche pour moi(certaines choses dans la vie sont 2048 octets, mais c'est assez rare); ne devrait-il vraiment pas être varchar (max)?

7
répondu DrPizza 2014-07-28 13:06:13
DELETE
FROM
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );
7
répondu Teena 2017-03-17 09:20:30
CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)

INSERT INTO car(PersonId,CarId)
VALUES(1,2),(1,3),(1,2),(2,4)

--SELECT * FROM car

;WITH CTE as(
SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)

DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)
6
répondu AnandPhadke 2012-07-11 11:46:52
DELETE 
FROM MyTable
WHERE NOT EXISTS (
              SELECT min(RowID)
              FROM Mytable
              WHERE (SELECT RowID 
                     FROM Mytable
                     GROUP BY Col1, Col2, Col3
                     ))
               );
6
répondu Jayron Soares 2015-07-23 12:42:54

une Autre façon de faire :--

DELETE A
FROM   TABLE A,
       TABLE B
WHERE  A.COL1 = B.COL1
       AND A.COL2 = B.COL2
       AND A.UNIQUEFIELD > B.UNIQUEFIELD 
6
répondu yuvi 2016-02-02 06:59:27