Del. duplicate rows from a table having no pk

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

General SQL Server Forums

New to SQL Server Programming

Del. duplicate rows from a table having no pk

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2008-03-07 : 06:28:13

hi,

create TABLE #t ( id int,names varchar(50))
INSERT INTO #t VALUES(1,'master')
INSERT INTO #t VALUES(2,'tempdb')
INSERT INTO #t VALUES(2,'tempdb')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(1,'master')
SELECT * FROM #t

Iam using sql server 2005. I want to delete the duplicate records from the table.Please specify a method which is very good at performance.

Thanks in Advance..........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-07 : 06:31:44

[code]DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY id,names ORDER BY id) RowNo,
id,
names
FROM #t
)t
WHERE t.RowNo<>1[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-07 : 07:12:57

Be sure that you define a primary key on this table, and for *all* tables in your databases.

- Jeff
http://weblogs.sqlteam.com/JeffS

raky
Aged Yak Warrior

767 Posts

Posted - 2008-03-07 : 07:31:18

Thanks to all for their replies and suggestions...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-07 : 08:08:10

See what you can do with row_number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

raky
Aged Yak Warrior

767 Posts

Posted - 2008-03-07 : 08:14:16

quote:
Originally posted by madhivanan

See what you can do with row_number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Hi, I usually follow ur posts. thnks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-07 : 08:43:49

raky -- you are going to define primary keys on all of your tables now, right?

- Jeff
http://weblogs.sqlteam.com/JeffS

raky
Aged Yak Warrior

767 Posts

Posted - 2008-03-08 : 01:07:47

quote:
Originally posted by jsmith8858

raky -- you are going to define primary keys on all of your tables now, right?

- Jeff
http://weblogs.sqlteam.com/JeffS

surely...

amirmuthu
Starting Member

12 Posts

Posted - 2008-03-10 : 06:51:15

--------------Delete Duplicate Record---------------
SET ROWCOUNT 1

DELETE emp

FROM emp a

WHERE (SELECT COUNT(*) FROM emp b WHERE b.cid = a.cid AND b.cid =

a.cid) > 1

WHILE @@rowcount > 0

DELETE emp

FROM emp a

WHERE (SELECT COUNT(*) FROM emp b WHERE b.cid = a.cid AND b.cid=

a.cid) > 1

SET ROWCOUNT 0

mfdarvesh
Starting Member

10 Posts

Posted - 2008-08-26 : 08:42:18

amirmuthu, Thanks, it is very useful cursor, applied and worked correctly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-26 : 08:46:03

quote:
Originally posted by amirmuthu

--------------Delete Duplicate Record---------------
SET ROWCOUNT 1

DELETE emp

FROM emp a

WHERE (SELECT COUNT(*) FROM emp b WHERE b.cid = a.cid AND b.cid =

a.cid) > 1

WHILE @@rowcount > 0

DELETE emp

FROM emp a

WHERE (SELECT COUNT(*) FROM emp b WHERE b.cid = a.cid AND b.cid=

a.cid) > 1

SET ROWCOUNT 0

Must be the most slowest method ever?

Why did you think this is preferred over other methods?

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-26 : 09:51:29

I assume you really want to keep just one copy of each row.

set nocount on
create TABLE #t ( id int,names varchar(50))
INSERT INTO #t VALUES(1,'master')
INSERT INTO #t VALUES(2,'tempdb')
INSERT INTO #t VALUES(2,'tempdb')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(3,'model')
INSERT INTO #t VALUES(1,'master')
INSERT INTO #t VALUES(5,'msdb')
set nocount off

print 'Find rows with dupes'
select
	id ,names
into
	#t2
from
	#t
group by
	id ,names
having
	count(*) > 1
order by
	id ,names

print 'Delete rows with dups'
delete from #t
from
	#t
	join
	#t2
	on #t.id = #t2.id and #t.names = #t2.names

print 'Re-Insert rows'
insert into #t
select * from #t2 order by id,names

print 'Final #t'
SELECT * FROM #t
go
if object_id('tempdb..#t','u') is not null  drop table #t
if object_id('tempdb..#t2','u') is not null drop table #t2


Results:
Find rows with dupes

(3 row(s) affected)

Delete rows with dups

(7 row(s) affected)

Re-Insert rows

(3 row(s) affected)

Final #t
id          names                                              
----------- -------------------------------------------------- 
1           master
2           tempdb
3           model
5           msdb

(4 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources