#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    1
    Rep Power
    0

    Add Values to Existing Columns or Insert - Stored Procedure


    Hello all, Im new to Firebird, and kind of ripping my hair out, on how best to do this Firebird.

    Assume I have a table MyTotals:

    ** clientID (string)
    ** tMonth (int)
    txBytes (int)
    rxBytes (int)

    ** is Primary Key

    Now I call my stored Proc with the 4 values (clientID, Month, txBytes, and rxBytes)

    What I want to happen is if a row exists matching the Primary key, then add my txBytes and rxBytes provided in my SP to the existing values in that row, if a row does not exist, create one, with the values provided to the SP.

    What would be the best way to accomplish?

    Thanks for any help
    Shawn



    ** UPDATE:

    Im thinking MERGE would be the appropriate starting step here. Is this accurate, best way to do? Any room for improvement?

    merge into MyTotals m using (select rxBytes, txBytes)
    on m.clientID = :clientID and m.tMonth = :month
    when matched then
    update set rxBytes = m.rxBytes + :rxBytes,
    txBytes = m.txBytes + :txBytes
    when not matched then
    insert (clientID,tMonth,txBytes,rxBytes) values (:clientID,:tMonth,:rxBytes,:txBytes)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    You can try this command:
    update or insert into MyTotals (clientID,tMonth,txBytes,rxBytes)
    values (:clientID,:tMonth,:rxBytes,:txBytes)
    matching (clientID,tMonth);

    I hope is not too late.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Originally Posted by costin_mentor
    You can try this command:
    update or insert into MyTotals (clientID,tMonth,txBytes,rxBytes)
    values (:clientID,:tMonth,:rxBytes,:txBytes)
    matching (clientID,tMonth);

    I hope is not too late.
    This will replace the values in txBytes,rxBytes if the row exists. It will not add the new values to the old values.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    Originally Posted by mIRCata
    This will replace the values in txBytes,rxBytes if the row exists. It will not add the new values to the old values.
    You are right, i didn't see that he wants to add new values to old values.

IMN logo majestic logo threadwatch logo seochat tools logo