Excluir registros duplicados de uma tabela SQL sem uma chave primária

Eu tenho a tabela abaixo com os registros abaixo nele

create table employee ( EmpId number, EmpName varchar2(10), EmpSSN varchar2(11) ); insert into employee values(1, 'Jack', '555-55-5555'); insert into employee values (2, 'Joe', '555-56-5555'); insert into employee values (3, 'Fred', '555-57-5555'); insert into employee values (4, 'Mike', '555-58-5555'); insert into employee values (5, 'Cathy', '555-59-5555'); insert into employee values (6, 'Lisa', '555-70-5555'); insert into employee values (1, 'Jack', '555-55-5555'); insert into employee values (4, 'Mike', '555-58-5555'); insert into employee values (5, 'Cathy', '555-59-5555'); insert into employee values (6 ,'Lisa', '555-70-5555'); insert into employee values (5, 'Cathy', '555-59-5555'); insert into employee values (6, 'Lisa', '555-70-5555'); 

Eu não tenho nenhuma chave primária nesta tabela. Mas eu já tenho os registros acima na minha tabela. Eu quero remover os registros duplicados que tem o mesmo valor nos campos EmpId e EmpSSN.

Ex: Emp id 5

Qualquer um pode me ajudar a enquadrar uma consulta para excluir esses registros duplicados

desde já, obrigado

Adicione uma chave primária (código abaixo)

Execute a exclusão correta (código abaixo)

Considere por que você não gostaria de manter essa chave primária.


Assumindo MSSQL ou compatível:

 ALTER TABLE Employee ADD EmployeeID int identity(1,1) PRIMARY KEY; WHILE EXISTS (SELECT COUNT(*) FROM Employee GROUP BY EmpID, EmpSSN HAVING COUNT(*) > 1) BEGIN DELETE FROM Employee WHERE EmployeeID IN ( SELECT MIN(EmployeeID) as [DeleteID] FROM Employee GROUP BY EmpID, EmpSSN HAVING COUNT(*) > 1 ) END 

É muito simples. Eu tentei no SQL Server 2008

 DELETE SUB FROM (SELECT ROW_NUMBER() OVER (PARTITION BY EmpId, EmpName, EmpSSN ORDER BY EmpId) cnt FROM Employee) SUB WHERE SUB.cnt > 1 

Use o número da linha para diferenciar entre registros duplicados. Mantenha o primeiro número de linha para um EmpID / EmpSSN e exclua o resto:

  DELETE FROM Employee a WHERE ROW_NUMBER() <> ( SELECT MIN( ROW_NUMBER() ) FROM Employee b WHERE a.EmpID = b.EmpID AND a.EmpSSN = b.EmpSSN ) 
 With duplicates As (Select *, ROW_NUMBER() Over (PARTITION by EmpID,EmpSSN Order by EmpID,EmpSSN) as Duplicate From Employee) delete From duplicates Where Duplicate > 1 ; 

Isso atualizará a tabela e removerá todas as duplicatas da tabela!

 select distinct * into newtablename from oldtablename 

Agora, o newtablename não terá registros duplicados.

Basta alterar o nome da tabela ( newtablename ) pressionando F2 no explorador de objects no sql server.

Você pode criar uma tabela temporária #tempemployee contendo uma select distinct de sua tabela de employee . Em seguida, delete from employee . Em seguida, insert into employee select from #tempemployee .

Como o Josh disse – mesmo que você conheça as duplicatas , excluí-las será impossível, já que você não pode realmente se referir a um registro específico se for uma duplicata exata de outro registro.

Código

 DELETE DUP FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY Clientid ORDER BY Clientid ) AS Val FROM ClientMaster ) DUP WHERE DUP.Val > 1 

Explicação

Use uma consulta interna para construir uma visualização sobre a tabela que inclua um campo baseado em Row_Number() , particionado pelas colunas que você deseja que sejam exclusivas.

Exclua dos resultados dessa consulta interna, selecionando qualquer coisa que não tenha um número de linha de 1; ou seja, as duplicatas; não o original.

A cláusula order by da function da janela row_number é necessária para uma syntax válida; você pode colocar qualquer nome de coluna aqui. Se você deseja alterar qual dos resultados é tratado como uma duplicata (por exemplo, manter o mais antigo ou mais recente, etc), então as colunas usadas aqui são importantes; ou seja, você quer especificar a ordem de forma que o registro que você deseja manter venha em primeiro lugar no resultado.

Se você não quiser criar uma nova chave primária, poderá usar o comando TOP no SQL Server:

 declare @ID int while EXISTS(select count(*) from Employee group by EmpId having count(*)> 1) begin select top 1 @ID = EmpId from Employee group by EmpId having count(*) > 1 DELETE TOP(1) FROM Employee WHERE EmpId = @ID end 

Seu uso fácil abaixo da consulta

 WITH Dups AS ( SELECT col1,col2,col3, ROW_NUMBER() OVER(PARTITION BY col1,col2,col3 ORDER BY (SELECT 0)) AS rn FROM mytable ) DELETE FROM Dups WHERE rn > 1 

Eu não sou um especialista em SQL, então tenha paciência comigo. Tenho certeza de que você obterá uma resposta melhor em breve. Veja como você pode encontrar os registros duplicados.

 select t1.empid, t1.empssn, count(*) from employee as t1 inner join employee as t2 on (t1.empid=t2.empid and t1.empssn = t2.empssn) group by t1.empid, t1.empssn having count(*) > 1 

Excluí-los será mais complicado porque não há nada nos dados que você poderia usar em uma instrução de exclusão para diferenciar as duplicatas. Eu suspeito que a resposta envolverá row_number () ou adicionando uma coluna de identidade.

  criar um índice clusterizado exclusivo Employee_idx
 no empregado (EmpId, EmpSSN)
 com ignore_dup_key 

Você pode descartar o índice se não precisar dele.

sem ID , sem rowcount() ou nenhuma temp table necessária ….

 WHILE ( SELECT COUNT(*) FROM TBLEMP WHERE EMPNO IN (SELECT empno from tblemp group by empno having count(empno)>1)) > 1 DELETE top(1) FROM TBLEMP WHERE EMPNO IN (SELECT empno from tblemp group by empno having count(empno)>1) 

há duas colunas no ID da tabela e nome onde os nomes estão repetindo com IDs diferentes, para que você possa usar esta consulta:. .

 DELETE FROM dbo.tbl1 WHERE id NOT IN ( Select MIN(Id) AS namecount FROM tbl1 GROUP BY Name ) 

Ter uma tabela de database sem Chave Primária é realmente e dirá PRÁTICA extremamente RUIM … então depois que você adicionar uma (ALTER TABLE)

Execute isso até não ver mais registros duplicados (esse é o objective de HAVING COUNT)

 DELETE FROM [TABLE_NAME] WHERE [Id] IN ( SELECT MAX([Id]) FROM [TABLE_NAME] GROUP BY [TARGET_COLUMN] HAVING COUNT(*) > 1 ) SELECT MAX([Id]),[TABLE_NAME], COUNT(*) AS dupeCount FROM [TABLE_NAME] GROUP BY [TABLE_NAME] HAVING COUNT(*) > 1 

MAX ([Id]) fará com que apague os registros mais recentes (aqueles adicionados após a primeira criação), caso você queira o oposto, no caso de precisar excluir os primeiros registros e deixar o último registro inserido, use MIN ([Id])

 select t1.* from employee t1, employee t2 where t1.empid=t2.empid and t1.empname = t2.empname and t1.salary = t2.salary group by t1.empid, t1.empname,t1.salary having count(*) > 1 
 DELETE FROM 'test' USING 'test' , 'test' as vtable WHERE test.id>vtable.id and test.common_column=vtable.common_column 

Usando isso, podemos remover registros duplicados

  Teste ALTER IGNORE TABLE
            ADICIONAR ÍNDICE ÚNICO 'teste' ('b'); 

@ aqui ‘b’ é o nome da coluna para uniqueness, @ aqui ‘test’ é o nome do índice.