• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • 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
0
aeg1s
Asked:
aeg1s
  • 2
1 Solution
 
ocgstylesCommented:
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:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011133.htm

so you can use this to return the new value:

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

- Keith
0
 
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?
0
 
ocgstylesCommented:
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:

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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