Delete duplicates without using RowId - Page 2 Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Delete duplicates without using RowId

  1. #11
    Join Date
    Dec 2001
    Originally posted by ronnie

    select account_id, trade_date, settlement_date, amount, quantity
    from trades
    group by account_id, trade_date, settlement_date, amount, quantity
    having count(*) > 1
    in oracle we use

    delete trades a
    where rowid = (select max(rowid) from trades where
    account_id = a.account_id and trade_date = a. trade_date and settlement_date = a.settlement_date and amount = a.amount and quantity = a.quantity )

    so if you dont want to use rowid you can create a temporary field with unique values ( may be generated withsequence ) and use that value instead of rowid.
    Santosh Jadhav
    8i OCP DBA

  2. #12
    Join Date
    May 2000
    Posted by Ronnie

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


    The verification of checks and balances should be taken care of your application.
    As per the RDBMS theory (principle), every row must be uniquely identified by a key. That is why we create PK on all tables.

    There is an exception, i.e for temporary table. In which we truncate the table after its usage.

    A real and good banking application would check the current balance with the balance at the time of transaction started.
    Time-00: Transaction started Balance $1000.00
    Store $1000 to a variable
    Time-01: Subtract $800.00
    Time-02: Update acct_table
    set balance = balance - $800
    where ac_id = id_num
    and balance = $1000;
    Time-03 if update fails, then some one has already withdraw money from the a/c. The balance is not $1000.Hence, the current transaction will not be executed. Raise error

    [Edited by tamilselvan on 05-22-2002 at 02:10 PM]

  3. #13
    Join Date
    May 2000
    Portsmouth, NH, USA

    Thumbs up

    Originally posted by mrchrispy
    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)?
    b) truncate the original tables and populate it from the new table.
    yeah, why can't you do this? A distinct list eliminates the duplicates ...

    - Magnus

  4. #14
    Join Date
    Mar 2001
    New York , New York
    Yes I can do that and eventually will do that.

    I was just wondering if there is a way to do it in a single statement and I guess that there is'nt.


    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