SQL update field to next number, then return value

Posted on 2006-05-15
Last Modified: 2012-08-13
I am working on a application for moving items around in our warehouse, and I need to assign a batch number for the inventory transaction. I need to lookup a value in our J.D. Edwards database to get the next number, then increment it and for the next person.

I can do this in two statements, but I want to do it in one so that the select and update happen at the same time so I do not have to worry about another user incrementing the number at the same time.

Anyone have experience with that type of statement?

Thanks in advance.

Mike Williams
ISeries V5R3M0, J.D. Edwards 8.10
Question by:aeg1s
    LVL 5

    Expert Comment

    hi aeq1s,

    since you are using version 8.1.4 or above, you can use the NEW TABLE or OLD TABLE clauses.  OLD TABLE and NEW TABLE allow you to see what values were updated by an update statement, or see the new values of the rows affected by the update, respectively.  

    more information here:

    so you can use this to return the new value:

    select batchno from new table (
      update myTable set batchno = batchno + 1

    - Keith

    Author Comment

    I tried the following SQL:

    select count from NEW TABLE (
    update ITROOM.UPCREF set upcref.count = upcref.count + 1 where LITM='SKSW50' and type='Item'

    and I get the error:

     [SQL0199] Keyword TABLE not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

    I am testing this by executing the SQL from the iSeries Navagator software.

    Any other idea's?
    LVL 5

    Accepted Solution

    it looks like this may not be supported on your system.

    you'll probably have to use a transaction then so you can lock that row:

    select count from itroom.upcref where for update
    update ITROOM.UPCREF set upcref.count = upcref.count + 1 where LITM='SKSW50' and type='Item'

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now