DELETE DUPLICATE RECORDS

Question

DELETE DUPLICATE RECORDS

Ayie

SSC Eights!

Points: 828
More actions
April 23, 2008 at 11:56 pm

#380146

Hi All
Anyone can help me how to delete a duplication records retain one value in one table
Can anyone give a script for deleting duplicates records.
Please help me, I'm a new in SQL
Thank you very

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

Googling 'SQL server delete duplicate records' brought up a bunch of articles, including http://support.microsoft.com/kb/139444. Search engines are your friends.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

Do you have a primary key on your table. If you do have a primary key, then the solution should be easy.

Prasad Bhogadi
www.inforaise.com

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Ariel Dimapilis (4/23/2008)
Hi All
Anyone can help me how to delete a duplication records retain one value in one table
Can anyone give a script for deleting duplicates records.
Please help me, I'm a new in SQL
Thank you very

Ariel,

Can you post the CREATE TABLE statement for the table in question, please. Also, please identify the primary key if it has one and which columns you use to determine if rows have dupes or not... thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vivien Xing SSChampion Points: 12297 More actions · Answer 4

May try these steps:

1. Select the duplicated records into a temporary table

Select * into mytable from yourtable where clause …

2. Delete both records (duplicated records) from yourtable

3. Insert 1 record back to yourtable from mytable

Need to have primary key or unique constraint to prevent this happening. If you do have PK or unique constraint, should find out why this happened.

Or you can copy the whole table if it is OK to do so and test the above on the copy of the table first.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

You cannot delete duplicates directly from a table unless you would like to delete rows, which are duplicated.

Instead, you can modify your table design, such as add a new column with unique value, such as IDENTITY.

Then, you can delete your duplicates.

After deleting duplicates, you can drop the newly created column.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

SQL ORACLE (4/29/2008)
You cannot delete duplicates directly from a table unless you would like to delete rows, which are duplicated.

Any bets? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

daniloantunez SSC Veteran Points: 247 More actions · Answer 7

You can try this

Select DISTINCT * into #table from table

GO

truncate table

GO

insert into table select *from #table

zombi SSCrazy Points: 2605 More actions · Answer 8

here is a perfect solution for u.....

just create a temporary table

then move your duplicate record to the temporary using group by clause so only unique instance will be there,

then remove duplicate records from your table by taking id from the temporary table.

Once u will be delete your records , just insert the unique instance from the temporary table to ur table and u wiil be done. enjoy.....!

tosscrosby SSCoach Points: 18045 More actions · Answer 9

Jeff Moden (4/29/2008)
SQL ORACLE (4/29/2008)
You cannot delete duplicates directly from a table unless you would like to delete rows, which are duplicated.
Any bets? 😉

Jeff, I am not doubting your ability - meaning no bet, I've seen lots of your scripts! - but I am curious to see your solution. I try and analyze most of the code you post (and others as well) and fit what I can into my skillset. Some of you on this site never cease to amaze me. Thanks.

-- You can't be late until you show up.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

Do not know if this will be completely applicable to your problem but here is a technique (found on another SSC forum and being repeated by me) to delete duplicates, which might help you to come up with a solution.

Demo table:

CREATE TABLE [dbo].[PartDemo](

[ProductID] [int] NULL,

[CustomerId] [int] NULL,

[OrderAmount] [int] NULL,

[OrderDate] [datetime] NULL,

[Keyvalue] [varchar](2) NULL

) ON [PRIMARY]

GO

The data:

20101155/1/2008 12:00:00 AM

20102555/2/2008 12:00:00 AM

10 1 33 5/3/2008 12:00:00 AM

5 3 4 5/4/2008 12:00:00 AM

The procedure:

;with numbered as(SELECT rowno=row_number() over

(partition by PRODUCTID order by PRODUCTID),PRODUCTID,keyvalue from PartDemo)

delete from numbered where rowno>1

This will delete the row containing ProductId = 20, OrderAmount=55

Note In this example if there were many duplicates (Productid=20) all but one would be deleted by executing this procedure once.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 11

thought I would expand on my previous reply, and demonstrate how to view what would happen to insure that a deletion using the code is truly what you want .... anyway added some additional data and then executed:

;with numbered as(SELECT rowno=row_number() over

(partition by PRODUCTID, Customerid order by PRODUCTID),PRODUCTID,CustomerId from PartDemo)

select * from numbered

This gave me:

rowno PRODUCTID CustomerId

120 101

220 101

320 101

420 101

120 102

220 102

320 102

420 102

520 102

Thus extending my check of "duplication" to the values in more than a single column of the table

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 12

tosscrosby (5/23/2008)
Jeff Moden (4/29/2008)
SQL ORACLE (4/29/2008)
You cannot delete duplicates directly from a table unless you would like to delete rows, which are duplicated.
Any bets? 😉
Jeff, I am not doubting your ability - meaning no bet, I've seen lots of your scripts! - but I am curious to see your solution. I try and analyze most of the code you post (and others as well) and fit what I can into my skillset. Some of you on this site never cease to amaze me. Thanks.

Sorry, Terry... I was being a bit of a smart a$$ on that response (must've been in a foul mood from work) and I apologize for that short response. I'll crank out an example tonight...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tosscrosby SSCoach Points: 18045 More actions · Answer 13

Not a problem. I thought maybe you were calling SQL ORACLE out for a challenge! I don't have any issues here with duplicates. I just thought if you already had something, I'd like to see it. Like I said, all the different exposures help me in the long run.

-- You can't be late until you show up.

sho-467341 Ten Centuries Points: 1062 More actions · Answer 14

hang on a min... this looks familiar!

would this help:

[Delete Duplicates]

http://www.sqlservercentral.com/questions/T-SQL/62798/

🙂