delete duplicates - 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 2005 Forums

Transact-SQL (2005)

delete duplicates

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-02-12 : 13:02:51

how can I delete all duplicates besides for the first 2 from a table - meaning compare by phone number and any duplicate phone number -delete all records but the first 2.

table is

user

and relavant field is homephone

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 13:25:33

[code]set rowcount 1

select null

while @@rowcount > 0
delete t1
from table1 as t1
where t1.col1 = (select top 1 col1 from table1 group by col1 having count(*) > 2) as x on x.col1 = t1.col1

set rowcount 0[/code]

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-02-12 : 13:38:57

but what will this count it by? exact records? I want the count to be my any phone numbers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 14:09:13

set rowcount 1

select null

while @@rowcount > 0
delete t1
from {YourTableNameHere} as t1
where t1.HomePhone = (select top 1 t2.HomePhone from {YourTableNameHere} AS t2 group by t2.HomePhone having count(*) > 2) as x on x.HomePhone = t1.HomePhone

set rowcount 0

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources