Deleting identical record in a database

Results 1 to 5 of 5

Thread: Deleting identical record in a database

  1. #1
    BuhBuh Guest

    Default Deleting identical record in a database

    Hi,<BR><BR> What is the best way to delete all the identical records in a SQL database? Example:<BR><BR>Value = 1,2,2,2,3,3,4,5,6,6<BR>After = 1,2,3,4,5,6<BR><BR>thanks a lot.<BR><BR><BR>

  2. #2
    MG Guest

    Default RE: Deleting identical record in a database

    You will need to create a temp table with the same layout. Then append all of the records to you temp table. Then run a insert back into you originol table using the Group By statement on the field that has duplicates. This will only output one record per "value"

  3. #3
    Steve Cimino Guest

    Default RE: Deleting identical record in a database

    Our DBA gave me this statement awhile back. I&#039m sure it could be modified to delete the records, but this shows us all the duplicates.<BR><BR>SELECT *<BR>FROM TableName<BR>WHERE uniqueidentifier in<BR>(SELECT uniqueidentifier<BR>FROM TableName<BR>GROUP BY uniqueidentifier<BR>HAVING count(*) &#062; 1)<BR>ORDER BY uniqueidentifier

  4. #4
    Join Date
    Dec 1969
    Los Angeles, CA

    Default RE: Deleting identical record in a database

    Woah the problem with your query is that you will get all the duplicate records. If you "modify" it to do a delete then it will delete all duplicate records?? <BR><BR>To me it looks like it will delete all those records which have a duplicate entry including the "original" one. It seems to me that you will get the id&#039s or all records which have duplicate values and then hit delete and all those id&#039s will be deleted.<BR><BR>Getting into it it does not seem to be that cut and dry. Looks like we will indeed need a loop as for a temp table i dont see the need. <BR><BR>To find a crude and quick method get the rows in one cursor and the count of duplicated in another and loop that many number of times - 1 for the duplicate records.<BR><BR>I hope someone tells me a better way just incase i have to do something like this in future. I doubt it, but one never knows<BR>

  5. #5
    Steve Cimino Guest

    Default RE: Deleting identical record in a database

    Yep, you sure are right. It doesn&#039t seem that cut and dry. I didn&#039t look into it. I have the code pasted in my "code helper" file on my desktop, and threw it up on here. I figured if this returns me all the dups, it probably would&#039ve been easy to change it to a delete (BTW--this is for tables without a primary key)<BR><BR>Don&#039t ever doubt when you need this stuff. A couple of years ago, I used to say "****! I remember having a sample of that awhile back. Wish I would&#039ve saved it." That&#039s why I have my "code helper" file (of which BillW has added to it considerably since I started posting in here a few months back).<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts