DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Delete Duplicate Record - Please Please

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Delete Duplicate Record - Please Please

    [Originally posted by Thaha]

    I have a table with duplicate values in a particular column only.

    EX:

    A˙ ˙ ˙ ˙ ˙ ˙  B
    --------------------
    2˙ ˙ ˙ ˙ ˙ ˙  XXXXX
    2˙ ˙ ˙ ˙ ˙ ˙  YYNN
    3˙ ˙ ˙ ˙ ˙ ˙  hhjjkkj
    8˙ ˙ ˙ ˙ ˙ ˙  fdfdfhgfh
    3˙ ˙ ˙ ˙ ˙ ˙  HHHHHHHHH
    8˙ ˙ ˙ ˙ ˙ ˙  jkjkjkjkjkjkjk˙


    Please tell me an SQL statement to Delete the repeating records.

    I need the first occurence (or any occurence) to remain in the table

    So after the execution of the query, it should be



    A˙ ˙ ˙ ˙ ˙ ˙  B
    --------------------
    2˙ ˙ ˙ ˙ ˙ ˙  XXXXX
    3˙ ˙ ˙ ˙ ˙ ˙  hhjjkkj
    8˙ ˙ ˙ ˙ ˙ ˙  fdfdfhgfh


    Thanx in Advance.

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Delete Duplicate Record - Please Please

    [Originally posted by allen]

    DELETE [Boat Information].Class, [Boat Information].[Registration Number]
    FROM [Boat Information]
    WHERE ((([Boat Information].Class) In (SELECT [Class] FROM [Boat Information] As Tmp GROUP BY [Class] HAVING Count(*)>1 )));[Class] HAVING Count(*)>1 )))
    ORDER BY [Boat Information].Class;



  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Delete Duplicate Record - Please Please

    [Originally posted by Larry Asher]

    I think the better question is why are you getting duplicate records and what can you do to prevent duplicate values?

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Delete Duplicate Record - Please Please

    [Originally posted by Thaha]

    Hi,

    Actually it was for a data conversion.


  5. #5
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Delete Duplicate Record - Please Please

    [Originally posted by Larry Asher]

    Hi,

    I expreienced tyhe same thing a few months ago when I started out here. I inherited a ten year old database with no primary keys or established relationships. It was working but what a mess!

    One of the first things I had to do was create the primary key. I wound up using an API cal to create a GUID and it is working just fine. Sure, there were some nice candidate fields but none of the data was consitent enough.

    Also, I create a routine to move the data from the old database to the new database and then to log any of the inconsistencies so I could have someone in the department review the data and find out if it was in fact relevant and determine a way to get it back into the new database.

    Part of the routine queried the new data table for a specific field and a specifiec value, just as you are talking about.

    Loop through each entry in the old database:

    strSQL = SELECT A FROM tblNewTableName WHERE A ='" & tblOldTable.A & "'"

    SET rs = cn.execute(strSQL)

    Then test for any return records:

    If rs.bof and rs.eof then
    ˙ ' The record does not exist
    ˙ ' Write code to add the new record
    Else
    ˙ ' The record already exist
    ˙ ' Log the data in a way that you can identify
    ˙ ' the record in the old database
    End If

    This will provide you with a way to avoid duplicate data and way to identify duplicate data in the old database.

    Ypu have probably already finished your project by now. But, if this was helpful, would have been, let me know.

    Good luck.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center