Como posso remover linhas duplicadas?

Outra maneira possível de fazer isto é

; 

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

Estou a usar {[[1]} acima porque é arbitrário qual a linha a preservar em caso de empate.

Para preservar a última ordem em RowID por exemplo, pode usar ORDER BY RowID DESC

Planos De Execução

A solução ROW_NUMBER dará sempre praticamente o mesmo plano, enquanto que a estratégia GROUP BY é mais flexível.

Os factores que poderiam favorecer a abordagem agregada de hash seriam

• não existe um índice útil nas colunas de particionamento
• relativamente menos grupos com relativamente mais duplicados em cada grupo

Em versões extremas deste segundo caso (se existirem muito poucos grupos com muitos duplicados em cada um), também se poderia considerar inserir simplesmente as linhas para manter numa nova tabela, então TRUNCATE-ing o original e copiá-las de volta para minimizar o registo em comparação com a remoção de uma proporção muito elevada das linhas.

Há um bom artigo sobre Remover Duplicados no site de suporte da Microsoft. É muito conservador-eles têm você fazer tudo em etapas separadas - mas deve funcionar bem contra mesas grandes.

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField

A seguinte pesquisa é útil para remover linhas duplicadas. A tabela neste exemplo tem ID como uma coluna de identidade e as colunas que têm dados duplicados são Column1, Column2 e 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) 

O seguinte programa mostra a utilização de GROUP BY, HAVING, ORDER BY em uma consulta, e retorna os resultados com coluna duplicada e sua contagem.

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC 

delete t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid

Postgres:

delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid

DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 1 

Isto irá apagar as linhas duplicadas, excepto a primeira linha

DELETE
FROM
    Mytable
WHERE
    RowID NOT IN (
        SELECT
            MIN(RowID)
        FROM
            Mytable
        GROUP BY
            Col1,
            Col2,
            Col3
    )

(http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server)

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)

Rápida e suja para apagar as linhas duplicadas exactas (para tabelas pequenas):

select  distinct * into t2 from t1;
delete from t1;
insert into t1 select *  from t2;
drop table t2;

Eu prefiro a subquery\tendo conta (*) > 1 solução para a junção interna porque eu achei mais fácil de ler e foi muito fácil de transformar em uma instrução selecionada para verificar o que seria excluído antes de executá-lo.

--DELETE FROM table1 
--WHERE id IN ( 
     SELECT MIN(id) FROM table1 
     GROUP BY col1, col2, col3 
     -- could add a WHERE clause here to further filter
     HAVING count(*) > 1
--)

SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable

Para Obter As Linhas Duplicadas:

SELECT
name, email, COUNT(*)
FROM 
users
GROUP BY
name, email
HAVING COUNT(*) > 1

Para apagar as linhas duplicadas:

DELETE users 
WHERE rowid NOT IN 
SELECT MIN(rowid)
FROM users
GROUP BY name, email);

Mais uma solução fácil pode ser encontrada no link colado aqui. Este é fácil de entender e parece ser eficaz para a maioria dos problemas semelhantes. É para o servidor SQL, mas o conceito usado é mais do que aceitável.

Aqui estão as partes relevantes da página ligada:

Considere estes dados:

EMPLOYEE_ID ATTENDANCE_DATE
A001    2011-01-01
A001    2011-01-01
A002    2011-01-01
A002    2011-01-01
A002    2011-01-01
A003    2011-01-01

Em primeiro Lugar, inserir uma coluna de identidade nesse quadro, utilizando o seguinte: código:

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

Use o seguinte código para o resolver:

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2

-- insert distinct values into temp
insert into #temp 
select distinct * 
from  tableName

-- delete from source
delete from tableName 

-- insert into source from temp
insert into tableName 
select * 
from #temp

rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!

PS: ao trabalhar em coisas como esta eu sempre uso uma transação, isso não só garante que tudo é executado como um todo, mas também me permite testar sem arriscar nada. Mas é claro que devias ter um reforço, só para ter a certeza...

Esta consulta mostrou um bom desempenho para mim:

DELETE tbl
FROM
    MyTable tbl
WHERE
    EXISTS (
        SELECT
            *
        FROM
            MyTable tbl2
        WHERE
            tbl2.SameValue = tbl.SameValue
        AND tbl.IdUniqueValue < tbl2.IdUniqueValue
    )

Suprimiu 1m de linhas em pouco mais de 30sec de uma tabela de 2m (50% duplicados)

Usar o etc. A idéia é juntar-se em uma ou mais colunas que formam um registro duplicado e, em seguida, remover o que você quiser:

;with cte as (
    select 
        min(PrimaryKey) as PrimaryKey
        UniqueColumn1,
        UniqueColumn2
    from dbo.DuplicatesTable 
    group by
        UniqueColumn1, UniqueColumn1
    having count(*) > 1
)
delete d
from dbo.DuplicatesTable d 
inner join cte on 
    d.PrimaryKey > cte.PrimaryKey and
    d.UniqueColumn1 = cte.UniqueColumn1 and 
    d.UniqueColumn2 = cte.UniqueColumn2;

Aqui está outro bom artigo sobreRemover Duplicados .

It discusses why its hard: " SQL is based on relational algebra, and duplicates cannot occur in relational algebra, because duplicates are not allowed in a set."

A solução da mesa temporária, e dois exemplos mysql.

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )

A outra maneira é Criar uma nova tabela com os mesmos campos e com um índice único. Então Mova todos os dados da tabela antiga para a tabela nova . Ignorar automaticamente o servidor SQL (existe também uma opção sobre o que fazer se houver um valor duplicado: ignorar, interromper ou sth) duplicar os valores. Então temos a mesma mesa sem linhas duplicadas. Se não quiser um índice único, depois dos dados de transferência, pode largá-lo .

Especialmente para maiores tabelas você pode usar o pacote DTS (SSIS package to import/export data) a fim de transferir todos os dados rapidamente para a sua nova tabela indexada unicamente. Para 7 milhões de filas é preciso apenas alguns minutos.

Utilize este

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
   As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1

DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)

1. Criar uma nova tabela em branco com a mesma estrutura

2. Executar uma pesquisa como esta

   INSERT INTO tc_category1
   SELECT *
   FROM tc_category
   GROUP BY category_id, application_id
   HAVING count(*) > 1
   

3. Execute então esta consulta

   INSERT INTO tc_category1
   SELECT *
   FROM tc_category
   GROUP BY category_id, application_id
   HAVING count(*) = 1
   

Esta é a maneira mais fácil de apagar o registo duplicado

 DELETE FROM tblemp WHERE id IN 
 (
  SELECT MIN(id) FROM tblemp
   GROUP BY  title HAVING COUNT(id)>1
 )

Http://askme.indianyouth.info/details/how-to-dumplicate-record-from-table-in-using-sql-105

SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0

A partir do nível de Aplicação (infelizmente). Concordo que a maneira adequada de evitar a duplicação é ao nível da base de dados através do uso de um índice único, mas no servidor SQL 2005, um índice é permitido ser apenas 900 bytes, e meu campo varchar(2048) sopra isso longe.

-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism 
ON stories 
after INSERT, UPDATE 
AS 
    DECLARE @cnt AS INT 

    SELECT @cnt = Count(*) 
    FROM   stories 
           INNER JOIN inserted 
                   ON ( stories.story = inserted.story 
                        AND stories.story_id != inserted.story_id ) 

    IF @cnt > 0 
      BEGIN 
          RAISERROR('plagiarism detected',16,1) 

          ROLLBACK TRANSACTION 
      END 

Também, varchar (2048) parece-me suspeito (algumas coisas na vida são 2048 bytes, mas é bastante incomum); não deveria realmente ser varchar(max)?

DELETE
FROM
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );

CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)

INSERT INTO car(PersonId,CarId)
VALUES(1,2),(1,3),(1,2),(2,4)

--SELECT * FROM car

;WITH CTE as(
SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)

DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)

DELETE 
FROM MyTable
WHERE NOT EXISTS (
              SELECT min(RowID)
              FROM Mytable
              WHERE (SELECT RowID 
                     FROM Mytable
                     GROUP BY Col1, Col2, Col3
                     ))
               );

DELETE A
FROM   TABLE A,
       TABLE B
WHERE  A.COL1 = B.COL1
       AND A.COL2 = B.COL2
       AND A.UNIQUEFIELD > B.UNIQUEFIELD