1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to avoid duplicate rows from being inserted in the table.

Discussion in 'SQL PL/SQL' started by vicks_vj@yahoo.com, Dec 21, 2009.

  1. vicks_vj@yahoo.com

    vicks_vj@yahoo.com Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    I have one table in which I want to restrict some records from being inserted. I don't want to put any checked constraints. e.g. consider following table

    transaction(
    id number primary key,
    txn_date timestamp(7),
    payee varchar2(40),
    amount number,
    memo varchar2(40),
    ref_num number
    )

    I want to write SQL which should not inset duplicate record.
    e.g.

    I have written one as bellow:

    insert into transaction
    select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
    (select count(*) from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
    payee='Payee1' and amount=12)=0;

    Can I use exists/not exists, which query will be more appropriate. (Please consider that fields which I am using to filter out the duplicate transactions does not contain primary key.)

    Can I write such SQL. Or do i check for duplicate rows one by one and then filter the duplicate records.

    Suggestions Plz...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have a declared primary key; you, technically, have no duplicate rows. Also consider this: what prevents a single payee from being associated with a 'duplicate' transaction amount? Nothing, actually. Any number of transactions can be entered for the same payee for the exact same amount, even on the same day. eBay transactions can do this; Paypal transactions can do likewise, as well as a large number of other types of transactions. Take this example:

    Payee2 is selling widgets on eBay for 12 units of currency, shipping included [ you don't specify which currency you're using so I'll keep this as ambiguous as you have done ]. Payee2 has 20,000,000 widgets to sell, and for the first day they are offered sells 10,000 widgets, all on individual transactions. According to your logic all of those would be 'duplicate' transactions; they're actually not duplicates at all as the transaction id is different for each one even though they are the same payee and amount. Any resale or fiinancial establishment can have such transaction lists for a single customer. Unless you are specifically refusing to allow one payee from receiving the same amount from more than one person your logic is faulty.

    Please explain why you consider such transactional activity 'duplicate'.
     
  3. vicks_vj@yahoo.com

    vicks_vj@yahoo.com Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for the reply.

    Actually, we are providing a feature in the application to allow the user to upload his bank transactions manually though Qfx/Ofx files(which are provided by the bank).

    So user might upload the same file multiple times(by mistake or purposely). I wanted to filter out such transactions from inserting into the table. So for filtering such transactions I am using transaction payee, amount and date etc.

    Suggestions???
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Each transaction should be uniquely identified from the bank by a transaction number; apparently your ID is a generated number having no significance to the transaction except to provide sequential, distinct indicators that a row was loaded. Have you not looked at these files to see what information is available to uniquely identify transactions? You should be using the same unique identifier as the bank to ensure that duplicate transactions are not loaded. Your criteria of payee, amount and transaction date is lacking as I proved in my previous response.