Results 1 to 6 of 6

Thread: Upsert

  1. #1
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81

    Unanswered: Upsert

    Hi,

    Where can I learn about UPSERT command ?
    Tx.
    --
    kukuk

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi kukuk,

    In the old (!) days you might write

    begin
    update <table> set <column> = <value> where <primarykey> = <key>;
    if sql%rowcount = 0 then
    insert into <table> (<column>,<column>) values (<key>,<value>);
    end if;
    end;

    Ie, update an existing row, but if it doesn't exist, insert one.

    Some db's have now introduced various syntaxes for doing it directly, ie upsert, merge etc. I believe 9i has such a beast, and would suggest the 9i docs, try otn.oracle.com

    Hth
    Bill

  3. #3
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Thank you, billm.

    I want to know what happens with triggers when I run this <upsert>.
    Let's say, my <upsert> updates 3 rows and inserts another 2.
    Let's say, I have different triggers after insert and after update.
    Will both of them fire ?
    --
    kukuk

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Kukuk,

    To be honest I don't know for certain.

    I would imagine that with FOR EACH ROW triggers that the correct trigger would fire, otherwise the trigger code would be illegal. Ie, in an after insert trigger, there are no :OLD values, and referencing them would cause an error. So certainly for these types of triggers I would imagine they have to fire correctly.

    For triggers fired after the whole operation, I'm not sure but I would think it isn't quite as important as with "for each row" triggers, as in a traditional bulk insert/update trigger you don't know which specific rows were touched anyway?

    The only thing I can suggest is to create a couple and find out? This begs the question is there a new trigger firing type called "after upsert on..." :-)

    Hth
    Bill

  5. #5
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81

    Thumbs up

    Thanks, Bill
    --
    kukuk

  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up

    Here is an example:

    MERGE INTO sales s
    USING new_sales n
    ON (s.sales_transaction_id = n.sales_transaction_id)
    WHEN MATCHED THEN
    UPDATE s_quantity = s_quantity + n_quantity, s_dollar = s_dollar + n_dollar
    WHEN NOT MATCHED THEN
    INSERT (sales_quantity_sold, sales_dollar_amount)
    VALUES (n.sales_quantity_sold, n.sales_dollar_amount);


    For more information follow this link Implementing an Efficient MERGE Operation


    Hope that helps,

    clio_usa
    OCP - DBA
    Visit our Web Site

Posting Permissions

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