DELETE DUPLICATE RECORDS

  • 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

  • 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

  • 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

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

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

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

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

  • You can try this

    Select DISTINCT * into #table from table

    GO

    truncate table

    GO

    insert into table select *from #table

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

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

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

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

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

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

  • hang on a min... this looks familiar!

    would this help:

    [Delete Duplicates]

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

    🙂

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

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