asked on

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

ocgstyles

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

aeg1s

ASKER

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?

ASKER CERTIFIED SOLUTION

ocgstyles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial