Eliminación de filas duplicadas de una tabla de SQL Server mediante un script

Versión del producto original: SQL Server
Número de KB original: 70956

Resumen

En este artículo se muestra cómo quitar filas duplicadas de una tabla en Microsoft SQL Server mediante un script de Transact-SQL (T-SQL). Describe dos métodos comunes: copiar una instancia de cada fila duplicada en una tabla independiente y usar la ROW_NUMBER función para eliminar las filas adicionales en su lugar. Use este artículo si una tabla contiene registros repetidos, por ejemplo, porque no tiene ninguna restricción única o clave principal. El método 2 es más sencillo porque opera sobre el mismo objeto.

Cree una tabla de ejemplo que contenga filas duplicadas

Para demostrar ambos métodos, cree una tabla de ejemplo denominada original_table que tenga una columna, key_valuey agregue datos duplicados:

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)

A continuación, use uno de los métodos siguientes para quitar las filas duplicadas de la tabla.

Método 1: Mover filas duplicadas a una tabla independiente y volver a insertar una copia

El método 1 usa dos tablas: original_table y una segunda tabla, duplicate_table, que el script crea y luego elimina. Para aplicar el script a otra tabla, reemplace original_table por el nombre de la tabla. Este método requiere reescribir la lógica de GROUP BY y COUNT si un duplicado se define por más de una columna.

Ejecute el siguiente script:

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

El script lleva a cabo las siguientes acciones en el orden que se especifica:

• Mueve una instancia de cualquier fila duplicada de la tabla original a una tabla de duplicados.
• Elimina todas las filas de la tabla original que también se encuentran en la tabla duplicada.
• Mueve las filas de la tabla duplicada de vuelta a la tabla original.
• Elimina la tabla duplicada.

Este método es sencillo. Sin embargo, requiere disponer de suficiente espacio disponible en la base de datos para crear temporalmente la tabla duplicada. Este método también incurre en sobrecarga porque va a mover los datos.

Además, si la tabla tiene una columna IDENTITY , debe usar SET IDENTITY_INSERT ON al restaurar los datos en la tabla original.

Método 2: Usar la función ROW_NUMBER para eliminar filas duplicadas

La función ROW_NUMBER, que se introdujo en Microsoft SQL Server 2005, hace que esta operación sea mucho más sencilla. Reemplace key_value por la columna o columnas que definen un duplicado.

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

El script lleva a cabo las siguientes acciones en el orden que se especifica:

• Usa la función ROW_NUMBER para particionar los datos en función de key_value, que puede ser una o varias columnas separadas por comas.
• Elimina todos los registros que recibieron un valor DupRank mayor que 1. Este valor especifica que los registros son duplicados.

Debido a la (SELECT NULL) expresión, el script no ordena los datos particionados según ninguna condición. Si la lógica para eliminar duplicados requiere elegir qué registros eliminar y cuáles mantener en función del criterio de ordenación de otras columnas, puede usar la ORDER BY expresión para hacerlo.

Mantener la fila más reciente al quitar duplicados

Para mantener la fila con el valor más reciente, ordene en orden descendente por esa columna. Por ejemplo, considere una hipotética employee_table que tenga más de una fila para el mismo empleado. El siguiente script mantiene solo la fila que tiene el valor más reciente updated_date :

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

La fila que tiene el valor más reciente updated_date recibe un DupRank de 1, por lo que el script lo mantiene y elimina las filas anteriores.

¿Por qué se recomienda el método 2?

El método 2 es sencillo y eficaz por estas razones:

• No requiere que copie temporalmente los registros duplicados a otra tabla.
• No requiere que unas la tabla original consigo misma (por ejemplo, mediante una subconsulta que devuelva todos los registros duplicados mediante una combinación de GROUP BY y HAVING).
• Para obtener el mejor rendimiento, debe tener un índice correspondiente en la tabla que usa key_value como clave de índice e incluye las columnas de ordenación que pueda haber usado en la expresión ORDER BY.

La ROW_NUMBER función está disponible en SQL Server 2005 y versiones posteriores. Si usa una versión de SQL Server que no admite la función ROW_NUMBER, use el método 1 o un método similar en su lugar.

¿Cómo puedo evitar filas duplicadas en una tabla?

Después de quitar los duplicados, puede ayudar a evitar nuevas filas duplicadas definiendo una restricción única o un índice único en la columna o columnas que identifican una fila.

Contenido relacionado

• DELETE (Transact-SQL)
• Cláusula OVER (Transact-SQL)
• USANDO common_table_expression (Transact-SQL)