Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    1

    Unanswered: puzzling query question

    Hello all.
    I'm trying to create a package that will search for duplicate records.
    first, i'm planning on creating a temporary table that will be populated by a query that searches for all entries with similar soundex values. the problem is this...
    the table gets populated this way...

    Identifier 1 | Identifier 2
    ------------------------
    123 | 456
    456 | 123


    both identifiers appear twice because they are duplicates of each other.
    how do i make one of them disappear?
    thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how do i make one of them disappear?
    WHERE ID1 > ID2
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Check out the following code. Please be careful, I have not tested it, but it should work.

    delete from my_table v
    where v.rowid in
    (select rowid
    from mytable a. mytable b
    where (a.id1 = b.id1 and a.id2=b.id2) or (a.id1 = b.id2 and a.id2 = b.id1)
    and a.rowid <> b.rowid
    and a.rowid < b.rowid);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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