This is an old bank, dbase that has been transferred to MSSQL . Home
How to leave only one occurrence in the case of id duplicated in a table?
| Id| Nome |
|-----------|
| 1 | JOSE |
| 1 | JOSE |
| 2 | MARIA |
| 2 | MARIA |
| 2 | MARIA |
You can use temporary tables to help you:
select distinct * into #tmp From tabela
delete from tabela
insert into tabela
select * from #tmp drop table #tmp
You can make a cursor like this.
insert into tabela values
(1 , 'JOSE'),
(1 , 'JOSE' ),
(2 , 'MARIA' ),
(2 , 'MARIA' ),
(1 , 'JOSE'),
(1 , 'JOSE' ),
(2 , 'MARIA' ),
(2 , 'MARIA' ),
(1 , 'JOSE'),
(1 , 'JOSE' ),
(2 , 'MARIA' ),
(3 , 'antonio' ),
(3 , 'antonio' ),
(3 , 'antonio' ),
(3 , 'antonio' ),
(3 , 'antonio' ),
(3 , 'antonio' ),
(2 , 'MARIA' )
DECLARE @name VARCHAR(100)
DECLARE @id int
DECLARE db_cursor CURSOR FOR
select * from tabela
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id , @name
WHILE @@FETCH_STATUS = 0
BEGIN
if( select count(id) from tabela t where id= @id group by Nome having count(id) = 1) = 1
FETCH NEXT FROM db_cursor INTO @id , @name
else
begin
delete top(1) t
from tabela t
join (
select count(id) as id, Nome from tabela t
group by Nome
having count(id) > 1
)d
on d.Nome = t.Nome
end
END
CLOSE db_cursor
DEALLOCATE db_cursor
select * from tabela ;
