• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:

SQL update field to next number, then return value

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
  • 2
1 Solution
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
aeg1sAuthor Commented:
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?
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'
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now