SQL STMT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL STMT

Author

Topic

objkiran
Starting Member

11 Posts

Posted - 2005-01-24 : 10:07:10

i have a table, no primary key so the table has duplicate records for same identifier. I need to leave one record and delete the duplicate records .

can some one help me.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-24 : 10:12:22

Full duplicates?
pick the column or columns which sould form the primary key and

set rowcount 1
select 1
while @@rowcount > 0
delete tbl
from tbl t1
join (select pk1, pk2 from tbl group by pk1, pk2 having count(*) > 1) t2
on t1.pk1 = t2.pk1
and t1.pk2 = t2.pk2

set rowcount 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-24 : 10:12:31

select *
from
(select * from MyTable
union
select * from MyTable
) t

Go with the flow & have fun! Else fight the flow

objkiran
Starting Member

11 Posts

Posted - 2005-01-24 : 10:31:03

hello nr,
the query which you gave me deletes all the duplicate records. but my goal is to leave one record and delete other for a id.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-24 : 11:22:31

Does it? It should leave one version of each duplicate row.

Of course it's always possible I've made a mistake.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources