duplicate records - 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

SQL Server Development (2000)

duplicate records

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-01-19 : 06:50:42

I have a table in the follwing format:
CREATE TABLE [dbo].[tbl] (
[boxID] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MsgID] [int] NULL ,
[FName] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[acctnum] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
now there are some duplicates in the table, the duplicates are not exact row duplicates, just the boxid and msgid combination us repeated
i need to clean this out tried using temp table does'nt work.

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-19 : 07:28:06

As always you have to decide which ones to keep

set rowcount 1
select 1
while @@rowcount > 0
delete tbl
from tbl t1
join (select boxid, msgid from tbl group by boxid, msgid having count(*) > 1) t2
on t1.boxid = t2.boxid and t1.msgid = t2.msgid
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.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-01-19 : 07:45:47

quote:
Originally posted by nr

As always you have to decide which ones to keep

set rowcount 1
select 1
while @@rowcount > 0
delete tbl
from tbl t1
join (select boxid, msgid from tbl group by boxid, msgid having count(*) > 1) t2
on t1.boxid = t2.boxid and t1.msgid = t2.msgid
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.

Thanks a lot.
I really owe u a beer now !!

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources