How do I remove duplicate records? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How do I remove duplicate records?

Author

Topic

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-04-06 : 12:27:03

I have a table that is basically two copies of itself (the first 238 rows are exactly the same as the second 238 rows). How do I delete just the duplicates?

Thank you!

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-04-06 : 12:31:33

I forgot to mention: I have a feeling I should use SELECT TOP 238 but I'm not sure exactly what to do with it....

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-06 : 12:46:50

is there a primary key? I'm assuming not...


Declare @t table (
	id int,
	data varchar(10)
)

Insert Into @t Select 1, 'A'

Insert Into @t Select 2, 'B'
Insert Into @t Select 2, 'B'

Insert Into @t Select 3, 'C'
Insert Into @t Select 3, 'C'
Insert Into @t Select 3, 'C'

Insert Into @t Select 4, 'D'
Insert Into @t Select 4, 'D'
Insert Into @t Select 4, 'D'
Insert Into @t Select 4, 'D'




Declare @RowCount int

Set @RowCount = 1

Select * From @t

While (@RowCount > 0)
Begin
	Set RowCount 1

	Delete A
	From @t A
	Inner Join
		(
		Select id
		From @t 
		Group By id
		Having count(*)>1
		) B
	On A.id = B.id

	Set @RowCount = @@RowCount

	Set RowCount 0
End


Select * From @t

Corey

I Has Returned!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 12:47:12

You could do this to see if in fact they are duplicates.

select distinct * from YourTable

To delete, assuming there are no keys you can use to identify between the original and duplicate, you can use row_number function as follows:

with cte as
(select 
   *, 
   row_number() over (partition by col1,col2,etc,colNn order by (select null)) as N 
from 
   YourTable
)
delete from cte where n > 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 12:48:25

I just figured out how to use this -->

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-06 : 12:49:35

quote:
Originally posted by sunitabeck

I just figured out how to use this -->

I find I have use of that often

Corey

I Has Returned!!

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-04-06 : 13:01:41

Yeah, unfortunately the other person who set up the table knows even less about SQL than I do! Everyone here uses Access but I'm trying to take it to the next level. Thanks for your help!

quote:
Originally posted by sunitabeck

I just figured out how to use this -->

Um...I don't get it....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 13:19:30

quote:
Originally posted by Aleph_0

quote:
Originally posted by sunitabeck

I just figured out how to use this -->

Um...I don't get it....

I didn't either. I had seen the term "sniped" used in various postings. And, I had seen people putting in this funny emoticon in their postings. I felt a little dumb asking "how do you do that?", so I kept my mouth shut. But then, I figured out that if you put the word sniped within square brackets, it shows up in the posting as that picture.

In this case Corey "sniped" me. He was trying to respond to your question at the same time as I was, and he posted it before I did. So my posting looks a little silly since the question has already been answered etc. etc. Hence I was "sniped" like this -->

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-06 : 13:22:08

there is also beer...

Corey

I Has Returned!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-06 : 13:38:32

If you don't have foreign keys referencing that table, you can also do:

SELECT DISTINCT * INTO #dupes FROM myTable
TRUNCATE TABLE myTable
INSERT myTable SELECT * FROM #dupes
DROP TABLE #dupes

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-06 : 13:42:16

quote:
Originally posted by Seventhnight

there is also beer...

No

for me, and unfortunately there doesn't seem to be any [redwine]

PS: Corey, I am telling you, if we discuss beer and other irrelevant and irreverent topics in a real technical forum, Graz or Rob or somone is going to ban you and me from SQLTeam forums pretty soon!!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-06 : 13:44:49

well.. honestly I don't drink

while

either... I'm more of a [bourbon]

guy...

Corey

I Has Returned!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-06 : 13:48:01

quote:
if we discuss beer and other irrelevant and irreverent topics in a real technical forum, Graz or Rob or somone is going to ban you and me from SQLTeam forums pretty soon!!!

We'd have to ban ourselves first as the worst offenders.

Although if you want to talk about nothing but

, you should post in The Yak Corral. But incidental

within a thread is perfectly fine, especially if it helped you answer the question.

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-04-07 : 15:02:10

Message received - future questions will make

absolutely relevant to the conversation!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources