Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    7

    Unanswered: Multiple update statements in a stored procedure

    Hi,

    I am still learning sql so take it easy on me

    I have several update statements that I have to run on a fairly regular basis and I would like to be able to make them all one action and based on my research a stored procedure seems like a good way to do it. So here is my current code that does not work the first update statement work fine but it doesn't like the syntax where I added the second update statement. This is on an IBM i in case that matters.

    CREATE PROCEDURE KELLYP/ACTIVATEPN
    (IN BRANCH NUM(2,0))
    MODIFIES SQL DATA
    UPDATE KELLYP/insmft A1 SET A1.SBNSI = 5 WHERE
    EXISTS (SELECT 'PN' FROM kellyp/nsi A2 WHERE A1.SUNFMT = A2.PN AND
    A1.SFRAN = 'NT' and a1.SBRAN = BRANCH and A2.BR = BRANCH)

    UPDATE KELLYP/INSMFT A3 SET A3.SMU1 = 200 WHERE EXISTS (SELECT 'PN'
    FROM KELLYP/NSI A4 WHERE A3.SUNFMT = A4.PN AND A3.SFRAN = 'NT' AND A
    3.SBRAN = BRANCH AND A4.BR = BRANCH)

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Modifies sql data
    begin atomic
    update ... ;
    update ... ;
    end
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    if KELLYP/insmft and KELLYP/INSMFT are the same table,
    Why not use one statement to perform the update action:
    Code:
    UPDATE KELLYP/insmft A1 SET A1.SBNSI = 5,A1.SMU1 = 200
    WHERE EXISTS (SELECT 'PN' FROM kellyp/nsi A2 
    WHERE A1.SUNFMT = A2.PN
     AND A1.SFRAN = 'NT' 
     and a1.SBRAN = BRANCH and A2.BR = BRANCH)

  4. #4
    Join Date
    Nov 2012
    Posts
    7
    Quote Originally Posted by aflorin27 View Post
    Modifies sql data
    begin atomic
    update ... ;
    update ... ;
    end

    That's great does the atomic mean that if one of the updates fails it rolls back all changes?

  5. #5
    Join Date
    Nov 2012
    Posts
    7
    Quote Originally Posted by fengsun2 View Post
    if KELLYP/insmft and KELLYP/INSMFT are the same table,
    Why not use one statement to perform the update action:
    Code:
    UPDATE KELLYP/insmft A1 SET A1.SBNSI = 5,A1.SMU1 = 200
    WHERE EXISTS (SELECT 'PN' FROM kellyp/nsi A2 
    WHERE A1.SUNFMT = A2.PN
     AND A1.SFRAN = 'NT' 
     and a1.SBRAN = BRANCH and A2.BR = BRANCH)

    This was my next question thank you for the info!

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by kelly89 View Post
    That's great does the atomic mean that if one of the updates fails it rolls back all changes?
    Google search: http://www.dbforums.com/db2/1664995-...mic-begin.html
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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