#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
    10
    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
    234
    Rep Power
    15
    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
    10
    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