Deleting Duplicate Record in Production

  • 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?

    ---

  • 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.
  • 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]

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

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