Comment supprimer des lignes en double sur le serveur SQL?

J’aime les CTE et ROW_NUMBER car les deux combinés nous permettent de voir quelles lignes sont supprimées (ou mises à jour), donc il suffit de changer le DELETE FROM CTE... en SELECT * FROM CTE :

 WITH CTE AS( SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7], RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1) FROM dbo.Table1 ) DELETE FROM CTE WHERE RN > 1 

DEMO (le résultat est différent; je suppose que c’est dû à une faute de frappe de votre part)

 COL1 COL2 COL3 COL4 COL5 COL6 COL7 john 1 1 1 1 1 1 sally 2 2 2 2 2 2 

Cet exemple détermine les doublons par une seule colonne col1 raison de la PARTITION BY col1 . Si vous souhaitez inclure plusieurs colonnes, ajoutez-les simplement à PARTITION BY :

 ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn) 

Je préférerais CTE pour supprimer les lignes en double de la table du serveur SQL

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

en gardant 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 garder 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) 

Sans utiliser CTE et ROW_NUMBER() vous pouvez simplement supprimer les enregistrements en utilisant simplement le group by avec la fonction MAX .

 DELETE FROM MyDuplicateTable WHERE ID NOT IN ( SELECT MAX(ID) FROM MyDuplicateTable GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3) 

 DELETE from search where id not in ( select min(id) from search group by url having count(*)=1 union SELECT min(id) FROM search group by url having count(*) > 1 ) 

Microsoft dispose d’un guide très pratique sur la façon de supprimer les doublons. Découvrez http://support.microsoft.com/kb/139444

En résumé, voici le moyen le plus simple de supprimer des doublons lorsque vous n’avez que quelques lignes à supprimer:

 SET rowcount 1; DELETE FROM t1 WHERE myprimarykey=1; 

myprimarykey est l’identifiant de la ligne.

J’ai mis rowcount à 1 car je n’avais que deux lignes dupliquées. Si trois lignes avaient été dupliquées, j’aurais défini rowcount à 2 pour qu’il supprime les deux premières vues et n’en laisse qu’une dans la table t1.

J’espère que ça aide quelqu’un

S’il vous plaît voir la manière de suppression ci-dessous aussi.

 Declare @table table (col1 varchar(10),col2 int,col3 int, col4 int, col5 int, col6 int, col7 int) Insert into @table values ('john',1,1,1,1,1,1), ('john',1,1,1,1,1,1), ('sally',2,2,2,2,2,2), ('sally',2,2,2,2,2,2) 

Créé un exemple de table nommé @table et l’a chargé avec des données données.

 Delete aliasName from ( Select *, ROW_NUMBER() over (Partition by col1,col2,col3,col4,col5,col6,col7 order by col1) as rowNumber From @table) aliasName Where rowNumber > 1 Select * from @table 

Remarque: Si vous donnez toutes les colonnes de la Partition by partie, alors l’ order by n’est pas très important.

Je sais, la question est posée il y a trois ans, et ma réponse est une autre version de ce que Tim a posté.

Si vous n’avez pas de références, comme les clés étrangères, vous pouvez le faire. Je le fais beaucoup lorsque je teste des preuves de concept et que les données de test sont dupliquées.

SELECT DISTINCT [col1], [col2], [col3], [col4], [col5], [col6], [col7]

INTO [newTable]

;

Aller dans l’explorateur d’objects et supprimer l’ancienne table.

Renommez la nouvelle table avec le nom de l’ancienne table.

 with myCTE as ( select productName,ROW_NUMBER() over(PARTITION BY productName order by slno) as Duplicate from productDetails ) Delete from myCTE where Duplicate>1 

 -- this query will keep only one instance of a duplicate record. ;WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2, col3-- based on what? --can be multiple columns ORDER BY ( SELECT 0)) RN FROM Mytable) delete FROM cte WHERE RN > 1 

En référence à https://support.microsoft.com/en-us/help/139444/how-to-remove-duplicate-rows-from-a-table-in-sql-server

L’idée de supprimer les doublons implique

• a) Protéger les lignes qui ne sont pas en double
• b) Conservez l’une des nombreuses lignes qualifiées comme des doublons.

Pas à pas

• 1) Identifiez d’abord les lignes correspondant à la définition du doublon et insérez-les dans la table temporaire, par exemple #tableAll.
• 2) Sélectionnez des lignes non dupliquées (lignes simples) ou distinctes dans la table temporaire, par exemple #tableUnique.
• 3) Supprimer de la table source en joignant #tableAll pour supprimer les doublons.
• 4) Insérez dans la table source toutes les lignes de #tableUnique.
• 5) Drop #tableAll et #tableUnique

Si vous avez la possibilité d’append une colonne à la table temporairement, cette solution a fonctionné pour moi:

 ALTER TABLE dbo.DUPPEDTABLE ADD RowID INT NOT NULL IDENTITY(1,1) 

Puis effectuez un DELETE en utilisant une combinaison de MIN et de GROUP BY

 DELETE b FROM dbo.DUPPEDTABLE b WHERE b.RowID NOT IN ( SELECT MIN(RowID) AS RowID FROM dbo.DUPPEDTABLE a WITH (NOLOCK) GROUP BY a.ITEM_NUMBER, a.CHARACTERISTIC, a.INTVALUE, a.FLOATVALUE, a.STRINGVALUE ); 

Vérifiez que la suppression a été effectuée correctement:

 SELECT a.ITEM_NUMBER, a.CHARACTERISTIC, a.INTVALUE, a.FLOATVALUE, a.STRINGVALUE, COUNT(*)--MIN(RowID) AS RowID FROM dbo.DUPPEDTABLE a WITH (NOLOCK) GROUP BY a.ITEM_NUMBER, a.CHARACTERISTIC, a.INTVALUE, a.FLOATVALUE, a.STRINGVALUE ORDER BY COUNT(*) DESC 

Le résultat ne doit comporter aucune ligne dont le nombre est supérieur à 1. Enfin, supprimez la colonne rowid:

 ALTER TABLE dbo.DUPPEDTABLE DROP COLUMN RowID; 

Une autre façon de supprimer les lignes dupliquées sans perdre d’informations en une seule étape est la suivante:

 delete from dublicated_table t1 (nolock) join ( select t2.dublicated_field , min(len(t2.field_kept)) as min_field_kept from dublicated_table t2 (nolock) group by t2.dublicated_field having COUNT(*)>1 ) t3 on t1.dublicated_field=t3.dublicated_field and len(t1.field_kept)=t3.min_field_kept 

Après avoir essayé la solution suggérée ci-dessus, cela fonctionne pour les petites tables moyennes. Je peux suggérer cette solution pour de très grandes tables. car il s’exécute en itérations.

1. Supprimer toutes les vues de dépendance du LargeSourceTable
2. Vous pouvez trouver les dépendances en utilisant sql managment studio, faites un clic droit sur la table et cliquez sur “Afficher les dépendances”
3. Renommez la table:
4. sp_rename 'LargeSourceTable', 'LargeSourceTable_Temp'; GO
5. Créez à nouveau le LargeSourceTable , mais maintenant, ajoutez une clé primaire avec toutes les colonnes qui définissent les doublons add WITH (IGNORE_DUP_KEY = ON)

6. Par exemple:

   CREATE TABLE [dbo].[LargeSourceTable] ( ID int IDENTITY(1,1), [CreateDate] DATETIME CONSTRAINT [DF_LargeSourceTable_CreateDate] DEFAULT (getdate()) NOT NULL, [Column1] CHAR (36) NOT NULL, [Column2] NVARCHAR (100) NOT NULL, [Column3] CHAR (36) NOT NULL, PRIMARY KEY (Column1, Column2) WITH (IGNORE_DUP_KEY = ON) ); GO

7. Créez à nouveau les vues que vous avez supprimées en premier lieu pour la nouvelle table créée

8. Maintenant, exécutez le script SQL suivant, vous verrez les résultats dans 1 000 000 de lignes par page, vous pouvez modifier le numéro de ligne par page pour voir les résultats plus souvent.

9. Notez que je mets le IDENTITY_INSERT sous et hors tension car l’une des colonnes contient un identifiant incrémentiel automatique, que je copie également

SET IDENTITY_INSERT LargeSourceTable ON DECLARE @PageNumber AS INT, @RowspPage AS INT DECLARE @TotalRows AS INT declare @dt varchar(19) SET @PageNumber = 0 SET @RowspPage = 1000000

 While ((@PageNumber - 1) * @RowspPage < @TotalRows ) Begin begin transaction tran_inner ; with cte as ( SELECT * FROM LargeSourceTable_TEMP ORDER BY ID OFFSET ((@PageNumber) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY ) INSERT INTO LargeSourceTable ( ID ,[CreateDate] ,[Column1] ,[Column2] ,[Column3] ) select ID ,[CreateDate] ,[Column1] ,[Column2] ,[Column3] from cte commit transaction tran_inner PRINT 'Page: ' + convert(varchar(10), @PageNumber) PRINT 'Transfered: ' + convert(varchar(20), @PageNumber * @RowspPage) PRINT 'Of: ' + convert(varchar(20), @TotalRows) SELECT @dt = convert(varchar(19), getdate(), 121) RAISERROR('Inserted on: %s', 0, 1, @dt) WITH NOWAIT SET @PageNumber = @PageNumber + 1 End 

SET IDENTITY_INSERT LargeSourceTable OFF

Oh wow, je me sens tellement stupide de préparer toutes ces réponses, elles sont comme la réponse des experts avec tous les CTE et les tables temporaires, etc.

Et tout ce que j’ai fait pour le faire fonctionner était simplement agrégé la colonne ID en utilisant MAX.

 DELETE FROM table WHERE col1 IN ( SELECT MAX(id) FROM table GROUP BY id HAVING ( COUNT(col1) > 1 ) ) 

REMARQUE: vous devrez peut-être l’exécuter plusieurs fois pour supprimer les doublons, car cela ne supprimera qu’un ensemble de lignes en double à la fois.

Si vous pouvez trouver le nombre de lignes en double, par exemple vous avez une ligne en double, utilisez cette commande

 SET rowcount n-1 DELETE FROM your_table WHERE (spacial condition) 

pour plus d’informations je suggère cette