repetidos - eliminar registros duplicados sql server 2008 r2

Cómo eliminar filas duplicadas sin identificador único

Question

Tengo filas duplicadas en mi tabla y quiero eliminar duplicados de la manera más eficiente ya que la tabla es grande. Después de algunas investigaciones, he llegado a esta consulta:

WITH TempEmp AS
(
SELECT name, ROW_NUMBER() OVER(PARTITION by name, address, zipcode ORDER BY name) AS duplicateRecCount
FROM mytable
)
-- Now Delete Duplicate Records
DELETE FROM TempEmp
WHERE duplicateRecCount > 1;

Pero solo funciona en SQL, no en Netezza. Parece que no le gusta el DELETE después de la cláusula WITH ?

Answer 1

Daré una solución simple. Primero, copie una fila (Para copiar esa fila, haga clic en la columna generada sql). Ahora elimine todas las filas que dupliquen. Ahora, abra la opción de edición de 200 filas del servidor sql y luego pegue la fila que haya copiado anteriormente.

Answer 2

De la documentación, eliminar filas duplicadas

Una pregunta frecuente en el IRC es cómo eliminar las filas que están duplicadas en un conjunto de columnas, manteniendo solo el que tiene la ID más baja. Esta consulta lo hace para todas las filas de tablename que tengan la misma columna1, columna2 y columna3.

DELETE FROM tablename
WHERE id IN (SELECT id
FROM (SELECT id,
ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
FROM tablename) t
WHERE t.rnum > 1);

A veces se usa un campo de marca de tiempo en lugar de un campo de ID.

Answer 3

En un mundo perfecto, cada mesa tiene un identificador único de algún tipo.
En ausencia de una columna única (o una combinación de ctid ), use la columna ctid :

DELETE FROM tbl
WHERE ctid NOT IN (
SELECT min(ctid) -- ctid is NOT NULL by definition
FROM tbl
GROUP BY name, address, zipcode); -- list columns defining duplicates

La consulta anterior es corta, listando convenientemente los nombres de las columnas solo una vez. NOT IN (SELECT ...) es un estilo de consulta complicado cuando los valores NULL pueden estar involucrados, pero la columna del sistema ctid nunca es NULL. Ver:

• Encuentra registros donde una unión no existe

Usar EXISTS como lo demuestra @Gordon es típicamente más rápido. Entonces, es una auto-unión con la cláusula USING como @isapir añadida más tarde . Ambos deberían dar como resultado el mismo plan de consulta.

Pero tenga en cuenta una diferencia importante : estas otras consultas tratan los valores NULL como no iguales , mientras que GROUP BY (o DISTINCT o DISTINCT ON () ) tratan los valores NULL como iguales. No importa si las columnas clave están definidas NOT NULL . De lo contrario, dependiendo de tu definición de "duplicado", necesitarás uno o el otro enfoque. O utilice IS NOT DISTINCT FROM en comparación de los valores (que pueden no ser capaces de usar algunos índices).

Renuncia:

ctid es un detalle de implementación interna de Postgres, no está en el estándar SQL y se puede cambiar entre versiones principales sin previo aviso (incluso si eso es muy poco probable). Sus valores pueden cambiar entre comandos debido a procesos en segundo plano o operaciones de escritura simultáneas (pero no dentro del mismo comando).

Relacionado:

• ¿Cómo puedo (o puedo) SELECCIONAR DISTINCT en múltiples columnas?

• Cómo usar la ubicación física de las filas (ROWID) en una declaración DELETE

Aparte:

El objetivo de una declaración DELETE no puede ser el CTE, solo la tabla subyacente. Eso es un derrame de SQL Server, como es todo su enfoque.

Answer 4

Esto es lo que se me ocurrió, usando un group by

DELETE FROM mytable
WHERE id NOT in (
SELECT MIN(id)
FROM mytable
GROUP BY name, address, zipcode
)

Elimina los duplicados, preservando el registro más antiguo que tiene duplicados.

Answer 5

La sintaxis válida se especifica en http://www.postgresql.org/docs/current/static/sql-delete.html

ALTERARía su tabla para agregar una identificación de clave primaria de autoincrementación única para que pueda ejecutar una consulta como la siguiente, que mantendrá el primero de cada conjunto de duplicados (es decir, el que tiene la identificación más baja). Tenga en cuenta que agregar la clave es un poco más complicado en Postgres que en otros DB.

DELETE FROM mytable d USING (
SELECT min(id), name, address, zip
FROM mytable
GROUP BY name, address, zip HAVING COUNT() > 1
) AS k
WHERE d.id <> k.id
AND d.name=k.name
AND d.address=k.address
AND d.zip=k.zip;

Answer 6

Me gusta la solución de @ erwin-brandstetter, pero quería mostrar una solución con la palabra clave USING :

DELETE FROM table_with_dups T1
USING table_with_dups T2
WHERE T1.ctid < T2.ctid -- delete the "older" ones
AND T1.name = T2.name -- list columns that define duplicates
AND T1.address = T2.address
AND T1.zipcode = T2.zipcode;

Si desea revisar los registros antes de eliminarlos, simplemente reemplace DELETE con SELECT * y USING con una coma,, es decir,

SELECT * FROM table_with_dups T1
, table_with_dups T2
WHERE T1.ctid < T2.ctid -- select the "older" ones
AND T1.name = T2.name -- list columns that define duplicates
AND T1.address = T2.address
AND T1.zipcode = T2.zipcode;

Actualización: Probé algunas de las diferentes soluciones aquí para la velocidad. Si no espera muchos duplicados, esta solución funciona mucho mejor que los que tienen una cláusula NOT IN (...) ya que generan muchas filas en la subconsulta.

Si reescribe la consulta para usar IN (...) entonces se realiza de manera similar a la solución presentada aquí, pero el código SQL se vuelve mucho menos conciso.

Actualización 2: si tiene valores NULL en una de las columnas clave (que realmente no debería IMO), puede usar COALESCE() en la condición para esa columna, por ejemplo

 AND COALESCE(T1.col_with_nulls, ''[NULL]'') = COALESCE(T2.col_with_nulls, ''[NULL]'')

Answer 7

Podemos usar una función de ventana para la eliminación muy efectiva de las filas duplicadas:

DELETE FROM tab
WHERE id IN (SELECT id
FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id
FROM tab) x
WHERE x.row_number > 1);

La versión optimizada de algunos PostgreSQL (con ctid):

DELETE FROM tab
WHERE ctid = ANY(ARRAY(SELECT ctid
FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid
FROM tab) x
WHERE x.row_number > 1));

Answer 8

Si desea mantener una fila fuera de las filas duplicadas en la tabla.

create table some_name_for_new_table as
(select * from (select *,row_number() over (partition by pk_id) row_n from
your_table_name_where_duplicates_are_present) a where row_n = 1);

Esto creará una tabla que puedes copiar.

Antes de copiar la tabla, elimine la columna ''row_n''

Answer 9

Si desea un identificador único para cada fila, puede agregar uno (una serie o un guid) y tratarlo como una clave sustituta .

CREATE TABLE thenames
( name text not null
, address text not null
, zipcode text not null
);
INSERT INTO thenames(name,address,zipcode) VALUES
(''James'', ''main street'', ''123'' )
,(''James'', ''main street'', ''123'' )
,(''James'', ''void street'', ''456'')
,(''Alice'', ''union square'' , ''123'')
;
SELECT*FROM thenames;

 -- add a surrogate key
ALTER TABLE thenames
ADD COLUMN seq serial NOT NULL PRIMARY KEY
;
SELECT*FROM thenames;
DELETE FROM thenames del
WHERE EXISTS(
SELECT*FROM thenames x
WHERE x.name=del.name
AND x.address=del.address
AND x.zipcode=del.zipcode
AND x.seq < del.seq
);
-- add the unique constrain,so that new dupplicates cannot be created in the future
ALTER TABLE thenames
ADD UNIQUE (name,address,zipcode)
;
SELECT*FROM thenames;

Answer 10

Si no tiene otro identificador único, puede usar ctid :

delete from mytable
where exists (select 1
from mytable t2
where t2.name = mytable.name and
t2.address = mytable.address and
t2.zip = mytable.zip and
t2.ctid > mytable.ctid
);

Es una buena idea tener una identificación única de auto incremento en cada mesa. Hacer una delete como esta es una razón importante por la cual.