Dubbele rijen uit een SQL Server tabel verwijderen met behulp van een script

Oorspronkelijke productversie: SQL Server
Oorspronkelijk KB-nummer: 70956

Samenvatting

In dit artikel wordt beschreven hoe u dubbele rijen uit een tabel in Microsoft SQL Server verwijdert met behulp van een T-SQL-script (Transact-SQL). Er worden twee veelvoorkomende methoden beschreven: één exemplaar van elke dubbele rij naar een afzonderlijke tabel kopiëren en de ROW_NUMBER functie gebruiken om de extra rijen ter plekke te verwijderen. Gebruik dit artikel als een tabel herhaalde records bevat, bijvoorbeeld omdat deze geen unieke beperking of primaire sleutel heeft. Methode 2 is eenvoudiger omdat deze in-place werkt.

Een voorbeeldtabel maken die dubbele rijen bevat

Als u beide methoden wilt demonstreren, maakt u een voorbeeldtabel met de naam original_table die één kolom heeft, key_value, en voegt u dubbele gegevens toe:

CREATE TABLE original_table (key_value int )

INSERT INTO original_table values (1)
INSERT INTO original_table values (1)
INSERT INTO original_table values (1)

INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)

Gebruik vervolgens een van de volgende methoden om de dubbele rijen uit de tabel te verwijderen.

Note

Met deze scripts worden gegevens definitief verwijderd. Maak een back-up van de tabel of database voordat u deze opdrachten uitvoert op productiegegevens.

Methode 1: Dubbele rijen naar een afzonderlijke tabel verplaatsen en één kopie opnieuw invoegen

Methode 1 maakt gebruik van twee tabellen: original_table en een tweede tabel, duplicate_tabledie door het script wordt gemaakt en vervolgens wordt verwijderd. Als u het script wilt toepassen op een andere tabel, vervangt u original_table door de tabelnaam. Voor deze methode moet de logica van GROUP BY en COUNT opnieuw worden geschreven als een duplicaat wordt gedefinieerd door meer dan één kolom.

Voer het volgende script uit:

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

Dit script voert de volgende acties uit in de gegeven volgorde:

  • Verplaatst één exemplaar van een willekeurige dubbele rij in de oorspronkelijke tabel naar een dubbele tabel.
  • Hiermee verwijdert u alle rijen uit de oorspronkelijke tabel die zich ook in de duplicaattabel bevinden.
  • Hiermee verplaatst u de rijen in de duplicaattabel terug naar de oorspronkelijke tabel.
  • Verwijdert de dubbele tabel.

Deze methode is eenvoudig. U moet echter voldoende ruimte in de database hebben om de dubbele tabel tijdelijk te bouwen. Deze methode veroorzaakt ook overhead omdat u de gegevens verplaatst.

Als uw tabel een identiteitskolom heeft, moet u SET IDENTITY_INSERT ON gebruiken wanneer u de gegevens terugzet naar de oorspronkelijke tabel.

Methode 2: Gebruik de functie ROW_NUMBER om dubbele rijen te verwijderen

De ROW_NUMBER functie, die is geïntroduceerd in Microsoft SQL Server 2005, maakt deze bewerking veel eenvoudiger. Vervang key_value door de kolom of kolommen die een duplicaat definiëren.

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1

Dit script voert de volgende acties uit in de gegeven volgorde:

  • Hiermee gebruikt u de ROW_NUMBER functie om de gegevens te partitioneren op basis van de key_value, die uit een of meer door komma's gescheiden kolommen kan bestaan.
  • Verwijdert alle records die een DupRank waarde hebben gekregen die groter is dan 1. Deze waarde geeft aan dat de records duplicaten zijn.

Vanwege de (SELECT NULL)-expressie sorteert het script de gepartitioneerde gegevens niet op basis van enige voorwaarde. Als voor uw logica voor het verwijderen van duplicaten moet worden gekozen welke records moeten worden verwijderd en welke records moeten worden behouden op basis van de sorteervolgorde van andere kolommen, kunt u de ORDER BY-expressie gebruiken om dit te doen.

De meest recente rij behouden wanneer u duplicaten verwijdert

Als u de rij met de meest recente waarde wilt behouden, sorteert u in aflopende volgorde op die kolom. Denk bijvoorbeeld aan een hypothetisch employee_table dat meer dan één rij voor dezelfde werknemer heeft. Met het volgende script blijft alleen de rij met de meest recente updated_date waarde behouden:

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY employee_name
              ORDER BY updated_date DESC
            )
FROM employee_table
) AS T
WHERE DupRank > 1

De rij met de meest recente updated_date waarde ontvangt een DupRank van 1, dus het script bewaart die en verwijdert de oudere rijen.

Methode 2 is eenvoudig en effectief om deze redenen:

  • U hoeft de dubbele records niet tijdelijk naar een andere tabel te kopiëren.
  • U hoeft de oorspronkelijke tabel niet met zichzelf samen te voegen (bijvoorbeeld door een subquery te gebruiken die alle dubbele records retourneert met behulp van een combinatie van GROUP BY en HAVING).
  • Voor de beste prestaties moet u een bijbehorende index hebben op de tabel die gebruikmaakt van de key_value als indexsleutel en eventuele sorteerkolommen bevat die u mogelijk in de ORDER BY-expressie hebt gebruikt.

De ROW_NUMBER functie is beschikbaar in SQL Server 2005 en nieuwere versies. Als u een versie van SQL Server gebruikt die de functie ROW_NUMBER niet ondersteunt, gebruikt u in plaats daarvan methode 1 of een vergelijkbare methode.

Hoe kan ik dubbele rijen in een tabel voorkomen?

Nadat u de duplicaten hebt verwijderd, kunt u nieuwe dubbele rijen voorkomen door een unique constraint of een unieke index te definiëren voor de kolom of kolommen die een rij identificeren.

Verwante onderwerpen

  • Laatst bijgewerkt om