Como apagar as linhas duplicadas no servidor sql?

Eu gosto de ETI e ROW_NUMBER como os dois combinados nos permitem ver quais as linhas são apagadas( ou atualizadas), portanto basta mudar o DELETE FROM CTE... paraSELECT * 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

demonstração (o resultado é diferente; presumo que seja devido a um erro da sua parte)

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

Este exemplo determina duplicados por uma única coluna col1 por causa do PARTITION BY col1. Se quiser incluir várias colunas, basta adicioná - las ao PARTITION BY:

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

Preferia que o CTE removesse linhas duplicadas da tabela do servidor SQL

Recomendo vivamente que siga este artigo:: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

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

Sem manter o 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)

Sem usar CTE e ROW_NUMBER() pode apagar os registos apenas usando o grupo com a função MAX aqui está e o exemplo

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

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
)

A Microsoft tem um guia vey ry puro sobre como remover duplicados. Check out http://support.microsoft.com/kb/139444

Em resumo, aqui está a maneira mais fácil de apagar duplicados quando você tem apenas algumas linhas para apagar:

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

o myprimarykey é o identificador da linha.

Eu ajustei o número de linhas para 1 porque só tinha duas linhas que foram duplicadas. Se eu tivesse 3 linhas duplicadas então eu teria configurado Número de linhas para 2 de modo que apaga os dois primeiros que vê e só deixa um na tabela t1.

Por favor, Veja também a forma de eliminação abaixo.

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)

Criou uma tabela de amostras chamada @table e carregou-a com dados indicados.

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: Se está a dar todas as colunas na parte Partition by, então order by não tem muito significado.

Eu sei, a pergunta é feita há três anos, e minha resposta é outra versão do que Tim postou, mas postar apenas incase é útil para qualquer um.

Seleccione o distinto [col1], [col2], [col3], [col4], [col5], [col6], [col7]

Em [newTable]

;

Vai ao explorador de objectos e apaga a mesa antiga.

Mudar o nome da nova tabela com o nome da velha tabela.

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

with myCTE
as

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

Com referência a https://support.microsoft.com/en-us/help/139444/how-to-remove-duplicate-rows-from-a-table-in-sql-server

A ideia de remover duplicados envolve

• a) proteger as linhas que não são duplicadas
• B) mantenha uma das muitas linhas que se qualificaram juntas como duplicadas.

Passo a Passo

• 1) primeiro identificar as linhas que satisfazem a definição de duplicado e introduzi-los na mesa temporária, diz # tableAll .
• 2) Seleccione as linhas não duplicadas(linhas simples) ou linhas distintas na tabela temp diz # tableUnique.
• 3) riscar o que não interessa na tabela de origem. duplicata.
• 4) Inserir na tabela de código todas as linhas de #tableUnique.
• 5) largar # tableAll e # tableUnique {[[9]}

Se você tem a capacidade de adicionar uma coluna à tabela temporariamente, esta foi uma solução que funcionou para mim:

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

Depois execute uma eliminação usando uma combinação de MIN e grupo por

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 se a eliminação foi efectuada 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 

O resultado não deve ter linhas com uma contagem superior a 1. Finalmente, remover a coluna do rowid:

ALTER TABLE dbo.DUPPEDTABLE DROP COLUMN RowID;

Depois de tentar a solução sugerida acima, isso funciona para pequenas tabelas médias. Posso sugerir essa solução para mesas muito grandes. desde que funciona em iterações.

1. largue todas as áreas de dependência do LargeSourceTable
2. poderá encontrar as dependências usando o estúdio de gestão sql, carregue com o botão direito na tabela e carregue em "ver dependências"
3. mudar o nome da tabela:
4. sp_rename 'LargeSourceTable', 'LargeSourceTable_Temp'; GO
5. Crie o LargeSourceTable de novo, mas agora, adicione uma chave primária com todas as colunas que definem as duplicações adicionar WITH (IGNORE_DUP_KEY = ON)

6. Por exemplo:

   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. Crie de novo as vistas que deixou cair em primeiro lugar para a nova tabela criada

8. Agora, execute o seguinte script sql, você vai ver os resultados em 1.000.000 linhas por página, você pode alterar o número de linha por página para ver os resultados mais frequentemente.

9. Note que eu activei e desactivei o IDENTITY_INSERT porque uma das colunas contém um incremental automático identificação, que também estou a copiar

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

Outra forma de remover as linhas publicadas sem perder informação num passo é como a seguinte:

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

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

Nota: poderá ter de a executar várias vezes para remover duplicado, uma vez que isto só irá apagar um conjunto de linhas duplicadas de cada vez.

Se conseguir encontrar o número de linhas duplicadas, por exemplo, tem uma linha n duplicada, então use este comando

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

Para mais informações sugiro isto