6
This is an old bank, dbase who was transferred to MSSQL.
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 |
2 answers
4
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
-
I would only use a concrete table instead of a temporary table. You can never be too careful. : ) Or else, if you were going to use a temporary one, use the one that is valid for any session: ##tmp
-
ai depends on the situation of the colleague who questioned, I would particularly use temporary. but I agree with your placement, caution is never too.
-
This would not work very well if you had dependency among other tables ....
-
1
he did not mention dependencies with other tables, I assume that sales relationships are just these tables mentioned.
-
I agree with @Hoppy. See the statement: "How do I leave only one occurrence in the case of duplicated id in a table?" The author cites "a table".
-
@Cantoni, the author did not mention, but what about the others who need a similar answer? I just said that if I had a relationship that didn’t work out... the right thing in a case like this would be a loop and check if there’s only one distinct record...
-
@Marconciliosouza the author did not mention if the MSSQL service is running, if he mentioned in the question I would indicate to him to call, as I interpreted the imposed question, the user has only one table and no relationship. There is no way to do a generic cleaning that works for all types of relationships. Each case can be a case.
-
1
@Marconciliosouza, it is difficult to come up with an answer that suits everyone. Even because the question would have to be different. Even when I need to search for a subject, I usually find several answers in the OS, when I can’t find everything in a single question, I merge the solutions.
-
1
@Cantoni, the last explanation I even accept ... ta less rude. is worth up to a vote ...
-
@Marconciliosouza I apologize if you misunderstood me, it was unintentional to be rude.
1
VC can do a cursor as follows..
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 ;
Browser other questions tagged sql sql-server normalization
You are not signed in. Login or sign up in order to post.
You want to delete all duplicate records leaving only one?
– Ricardo
Yeah, one of each.
– rubStackOverflow
Make a select distinct in all fields. Insert the result of this select into another table (auxiliary), delete the data from the original table and insert the data from the auxiliary table into the original. That should solve.
– cantoni