Delete duplicates without using RowId
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Delete duplicates without using RowId

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    Is there a way of deleting duplicate records without using the rowId column.

    Actually I want to delete duplicates in a sybase database.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Assuming you have a PK, count by your PK and delete the min() or max().
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by marist89
    Assuming you have a PK, count by your PK and delete the min() or max().
    Nope there is no primary key.

    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by ronnie
    Nope there is no primary key.
    How do you know you have duplicates, then?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by marist89
    Originally posted by ronnie
    Nope there is no primary key.
    How do you know you have duplicates, then?

    select account_id, trade_date, settlement_date, amount, quantity
    from trades
    group by account_id, trade_date, settlement_date, amount, quantity
    having count(*) > 1
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Oh, so you do have a PK.

    Using the exact query you specified, I would create a temporary table of all my unique combinations. Then, I would figure out which row I want to drop with a min/max function and put that in the temporary table too. Once I knew the rows I wanted to delete, I would delete them from my base table.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by marist89
    Oh, so you do have a PK.

    Using the exact query you specified, I would create a temporary table of all my unique combinations. Then, I would figure out which row I want to drop with a min/max function and put that in the temporary table too. Once I knew the rows I wanted to delete, I would delete them from my base table.
    Jeff,
    i dont have a PK on the table at all. It can have multiple records with the same account Id .

    Can it be done using one query/delete statement

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  8. #8
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    could you not create a table as select distinct (*) from original table and then either
    a) drop the original table and rename the new one (is this possible)?
    or
    b) truncate the original tables and populate it from the new table.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    A table without PK is not a table at all.

  10. #10
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by tamilselvan
    A table without PK is not a table at all.

    what if those are the requirements and you have to delete the data only after certain checks and balances are taken care of. :-)

    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

Posting Permissions

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


Click Here to Expand Forum to Full Width