Deleting Duplicate Record in Production

Question

Deleting Duplicate Record in Production

sqlusers

SSCrazy

Points: 2463
More actions
August 27, 2010 at 4:00 am

#144790

Hi,
In our Production database, sometimes back the data getting duplicated because of not deleteing the old records. At now only we have come to know that the data gets duplicated. We want to delete those duplicated datas.
Here what a concern is we have nearly more than 1 crore data in the table. From that we need to delete. Can suggest us how can we do this in Production?
---

Viewing 3 posts - 1 through 2 (of 2 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

sqlusers (8/27/2010)
Hi,
In our Production database, sometimes back the data getting duplicated because of not deleteing the old records. At now only we have come to know that the data gets duplicated. We want to delete those duplicated datas.
Here what a concern is we have nearly more than 1 crore data in the table. From that we need to delete. Can suggest us how can we do this in Production?

First you do it in a test database, once validated you go for production.

Plenty of scripts on the net.

My suggestion is to revise table design, proper architecture would prevent duplicate rows. Most probably the implementation of a single unique index wdould do the trick

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

After following the suggestion by PaulB-TheOneAndOnly

First you do it in a test database, once validated you go for production

You can use a CTE something like:

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

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

select * from numbered

You can change the select statement to DELETE WHERE rowno > 1

To repeat:

First you do it in a test database, once validated you go for production

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]