Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting Duplicate Record in Production Expand / Collapse
Author
Message
Posted Friday, August 27, 2010 4:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
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?

---
Post #976256
Posted Friday, August 27, 2010 11:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #976642
Posted Friday, August 27, 2010 12:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:50 PM
Points: 5,574, Visits: 24,823
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

Before posting a performance problem please read
Post #976663
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse