¿Cómo puedo eliminar filas duplicadas?
Necesito eliminar filas duplicadas de una tabla de SQL Server bastante grande (es decir, más de 300.000 filas).
Las filas, por supuesto, no serán duplicados perfectos debido a la existencia del RowIDcampo de identidad.
Mi mesa
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
¿Cómo puedo hacer esto?
Suponiendo que no haya valores nulos, usted tendrá GROUP BYlas columnas únicas y SELECTel MIN (or MAX)RowId como la fila a conservar. Luego, simplemente elimine todo lo que no tenga una identificación de fila:
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
En caso de que tenga un GUID en lugar de un número entero, puede reemplazar
MIN(RowId)
con
CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))
Otra posible forma de hacer esto es
;
--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;
Estoy usando ORDER BY (SELECT 0)arriba porque es arbitrario qué fila conservar en caso de empate.
Para conservar el último en RowIDorden, por ejemplo, podría utilizarORDER BY RowID DESC
Planes de ejecución
El plan de ejecución para esto suele ser más simple y eficiente que el de la respuesta aceptada, ya que no requiere la autounión.
Esto no es siempre el caso, sin embargo. Un lugar donde GROUP BYse podría preferir la solución son las situaciones en las que se elegiría un agregado hash en lugar de un agregado de flujo.
La ROW_NUMBERsolución siempre dará prácticamente el mismo plan, mientras que la GROUP BYestrategia es más flexible.
Los factores que podrían favorecer el enfoque del agregado de hash serían
- No hay índice útil en las columnas de partición
- relativamente menos grupos con relativamente más duplicados en cada grupo
En versiones extremas de este segundo caso (si hay muy pocos grupos con muchos duplicados en cada uno), también se podría considerar simplemente insertar las filas para mantenerlas en una nueva tabla, luego TRUNCATEcopiar el original y copiarlas nuevamente para minimizar el registro en comparación con eliminar una proporción muy alta de las filas.
Hay un buen artículo sobre cómo eliminar duplicados en el sitio de soporte de Microsoft. Es bastante conservador (te piden que hagas todo en pasos separados) pero debería funcionar bien en mesas grandes.
He usado autouniones para hacer esto en el pasado, aunque probablemente podría mejorarse con una cláusula HAVING:
DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField
AND dupes.secondDupField = fullTable.secondDupField
AND dupes.uniqueField > fullTable.uniqueField
La siguiente consulta es útil para eliminar filas duplicadas. La tabla de este ejemplo tiene IDcomo columna de identidad y las columnas que tienen datos duplicados son Column1, Column2y 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)
El siguiente script muestra el uso de GROUP BY,, en una consulta y devuelve los resultados con una columna duplicada y su recuento HAVING.ORDER BY
SELECT YourColumnName,
COUNT(*) TotalCount
FROM YourTableName
GROUP BY YourColumnName
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
