repetir - sql eliminar registros duplicados menos uno

¿Cómo eliminar filas duplicadas en el servidor SQL?

Question

¿Cómo puedo delete duplicate rows donde no existe un unique row id ?

Mi mesa es

col1  col2 col3 col4 col5 col6 col7
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

Quiero quedarme con lo siguiente después de la eliminación de duplicados:

john  1    1    1    1    1    1
sally 2    2    2    2    2    2

He intentado algunas consultas pero creo que dependen de una identificación de fila ya que no obtengo el resultado deseado. Por ejemplo:

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

Answer 1

Después de probar la solución sugerida anteriormente, eso funciona para tablas medianas pequeñas. Puedo sugerir esa solución para tablas muy grandes. ya que se ejecuta en iteraciones.

1. LargeSourceTable todas las vistas de dependencia de la LargeSourceTable
2. Puede encontrar las dependencias usando sql managment studio, haga clic derecho en la tabla y haga clic en "Ver dependencias"
3. Renombrar la tabla:
4. sp_rename ''LargeSourceTable'', ''LargeSourceTable_Temp''; GO
5. Cree de nuevo la LargeSourceTable , pero ahora, agregue una clave principal con todas las columnas que definen las duplicaciones agregue WITH (IGNORE_DUP_KEY = ON)

6. Por ejemplo:

   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. Cree de nuevo las vistas que colocó en primer lugar para la nueva tabla creada

8. Ahora, ejecute el siguiente script sql, verá los resultados en 1,000,000 de filas por página, puede cambiar el número de fila por página para ver los resultados con más frecuencia.

9. Tenga en cuenta que IDENTITY_INSERT y desactivé IDENTITY_INSERT porque una de las columnas contiene una identificación incremental automática, que también estoy copiando

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 select @TotalRows = count (*) from LargeSourceTable_TEMP

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

Answer 2

Me gustan los CTE y ROW_NUMBER ya que los dos combinados nos permiten ver qué filas se eliminan (o actualizan), por lo tanto, simplemente cambie DELETE FROM CTE... para 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 (el resultado es diferente; supongo que se debe a un error tipográfico de su parte)

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

Este ejemplo determina duplicados por una sola columna col1 debido a la PARTITION BY col1 . Si desea incluir varias columnas, simplemente agréguelas a la PARTITION BY :

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

Answer 3

Microsoft tiene una guía muy clara sobre cómo eliminar duplicados. Echa un vistazo a http://support.microsoft.com/kb/139444

En resumen, esta es la forma más fácil de eliminar duplicados cuando solo tiene que eliminar algunas filas:

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

myprimarykey es el identificador de la fila.

Establecí el número de filas en 1 porque solo tenía dos filas que estaban duplicadas. Si me hubieran duplicado 3 filas, habría establecido el número de filas en 2 para que elimine las dos primeras que ve y solo deje una en la tabla t1.

Espero que ayude a cualquiera

Answer 4

Oh, wow, me siento tan estúpido al preparar todas estas respuestas, son como las respuestas de los expertos con todos los CTE y la tabla temporal, etc.

Y todo lo que hice para que funcionara fue simplemente agregar la columna de ID usando MAX.

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

NOTA: es posible que deba ejecutarlo varias veces para eliminar duplicados, ya que esto solo eliminará un conjunto de filas duplicadas a la vez.

Answer 5

Otra forma de eliminar filas duplicadas sin perder información en un solo paso es como sigue:

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

Answer 6

Por favor, vea la siguiente forma de eliminación también.

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)

Creó una tabla de muestra llamada @table y la cargó con los datos dados.

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

Nota: Si está dando todas las columnas en la Partition by parte, order by no tiene mucho significado.

Lo sé, la pregunta se formuló hace tres años y mi respuesta es otra versión de lo que Tim ha publicado, pero publicar solo en el caso es útil para cualquiera.

Answer 7

Preferiría CTE para eliminar filas duplicadas de la tabla del servidor SQL

Recomiendo encarecidamente seguir este artículo: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

manteniendo 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

sin guardar 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)

Answer 8

Si no tiene referencias, como claves externas, puede hacer esto. Lo hago mucho cuando se prueban pruebas de concepto y los datos de prueba se duplican.

SELECCIONAR DISTINTO [col1], [col2], [col3], [col4], [col5], [col6], [col7]

EN [tabla nueva]

;

Entra en el explorador de objetos y borra la tabla antigua.

Cambie el nombre de la nueva tabla con el nombre de la tabla anterior.

Answer 9

Si puede encontrar el número de filas duplicadas, por ejemplo, tiene una fila duplicada, entonces use este comando

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

Para más información te sugiero here

Answer 10

Si tiene la capacidad de agregar una columna a la tabla temporalmente, esta fue una solución que funcionó para mí:

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

Luego realice un BORRADO utilizando una combinación de MIN y 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
                 );

Verifique que el BORRADO se realizó correctamente:

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 

El resultado no debe tener filas con un recuento mayor que 1. Finalmente, elimine la columna rowid:

ALTER TABLE dbo.DUPPEDTABLE DROP COLUMN RowID;

Answer 11

Sin usar CTE y ROW_NUMBER() , solo puede eliminar los registros simplemente usando el grupo por con la función MAX . Aquí hay un ejemplo.

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

Answer 12

Con referencia a https://support.microsoft.com/en-us/help/139444/how-to-remove-duplicate-rows-from-a-table-in-sql-server

La idea de eliminar duplicados implica

• a) Protegiendo aquellas filas que no están duplicadas
• b) Retener una de las muchas filas que se clasificaron como duplicadas.

Paso a paso

• 1) Primero identifique las filas que satisfacen la definición de duplicado e insértelas en la tabla temporal, por ejemplo, #tableAll.
• 2) Seleccione filas no duplicadas (filas individuales) o distintas en la tabla temporal, por ejemplo, #tableUnique.
• 3) Borre de la tabla de origen uniéndose a #tableAll para eliminar los duplicados.
• 4) Inserte en la tabla de origen todas las filas de #tableUnique.
• 5) Soltar #tableAll y #tableUnique

Answer 13

-- 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

Answer 14

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
)

Answer 15

with myCTE
as

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