Verificando e excluindo duplicidades com e sem chave

Verificando e excluindo duplicidades com e sem chave

/*-Verificar e apagar linhas duplicadas, repetidas

OBS.: nestes casos em que é necessário eliminar uma quantidade considerável de registros de uma tabela, é muito importante a realização de um backup antes da operação.

1° caso: se a tabela tiver uma chave única e quisermos apagar uma outra coluna que está se repetindo, uma possível solução seria deletar todos os registros filtrando menos (NOT IN) o de código mínimo agrupado GROUP BY pela coluna nome (se tiver mais colunas na tabela, sem grilo, é só adicionar ae), usando a função MIN

*/

set nocount on
declare @teste table (cod int, nome varchar(30))

insert into @teste (cod, nome) values (1, 'Brasil')
insert into @teste (cod, nome) values (2, 'Romenia')
insert into @teste (cod, nome) values (3, 'Brasil')
insert into @teste (cod, nome) values (4, 'Brasil')
insert into @teste (cod, nome) values (5, 'USA')
insert into @teste (cod, nome) values (6, 'Italia')
insert into @teste (cod, nome) values (7, 'USA')
insert into @teste (cod, nome) values (8, 'Brasil')
insert into @teste (cod, nome) values (9, 'USA')
insert into @teste (cod, nome) values (10, 'Argentina')
insert into @teste (cod, nome) values (11, 'Romenia')

--esse select aqui é do que vai ser mantido, logo é só fazer um NOT IN nestes códigos na instrução DELETE.
(select min(cod) from @teste group by nome)

--deletando

delete from @teste
where cod not in (select min(cod) from @teste group by nome)

select * from @teste

/*

2° caso: se a tabela tiver a linha inteira idêntica, neste caso usaremos uma tabela virtual para armazenar apenas uma ocorrência das linhas repetidas usando o comando distinct, apagaremos todos os repetidos na tabela e vamos reinserir somente uma ocorrência destes

*/

set nocount on
declare @teste table (cod int, nome varchar(30))

insert into @teste (cod, nome) values (1, 'Brasil')
insert into @teste (cod, nome) values (2, 'Romenia')
insert into @teste (cod, nome) values (1, 'Brasil')
insert into @teste (cod, nome) values (1, 'Brasil')
insert into @teste (cod, nome) values (3, 'USA')
insert into @teste (cod, nome) values (4, 'Italia')
insert into @teste (cod, nome) values (3, 'USA')
insert into @teste (cod, nome) values (1, 'Brasil')
insert into @teste (cod, nome) values (3, 'USA')
insert into @teste (cod, nome) values (5, 'Argentina')
insert into @teste (cod, nome) values (2, 'Romenia')

--verificando o numero de repetições
select cod, nome, count(*) as nro_repeticoes
from @teste
group by cod, nome
having count(*)>1

--armazenando somente uma ocorrência dos repetidos (distinct) na virtual #tempx

select distinct t.cod, t.nome into #tempx from @teste t
inner join (select cod, count(*) as nro_repeticoes
	from @teste
	group by cod
	having count(*)>1)

taux on t.cod = taux.cod

--apagando todos os repetidos

delete from @teste
where cod in (select distinct t.cod from @teste t
	inner join (select cod, count(*) as nro_repeticoes
 from @teste
 group by cod
 having count(*)>1) taux
	on t.cod = taux.cod
)

--reinserindo somente uma ocorrência dos repetidos na tabela principal a partir da tabela temporária #tempx

insert into @teste (cod, nome)
select cod, nome from #tempx

--destruindo a tabela virtual
drop table #tempx

--consultado a tabela depois das modificações
select * from @teste

Fonte/Autor/link

AUTOR: "Eriva_br"

Dúvidas, criticas, contribuições, correções e adições serão bem vindas.