CODEWITHSUNDEEP
sql-server
user1366794
user1366794

Reputation: 41

increment SQL field manually

I'm attempting to create a SAP UDO for product registrations. The UDO automatically creates two required fields "docentry" and "code." docentry auto-increments, code does not. I don't have accessing to the underlying database structure to fix that, but I can attach a query to the code field.

When a record is created, can I setup a query that would increment based on the previous row's value for code? I'm not looking for anything useful out of this field, but we will need to both batch import .csv files on a regular basis and also take direct submissions from our site, so I'm just looking for the easiest way to make it work.

Thank you.

EDIT from OP comment:

"We're using SQL server 2008 R2, and 'docentry' is the unique PK."

Upvotes: 0

Views: 908

Answers (1)

ElmoVanKielmo
ElmoVanKielmo

Reputation: 11290

UPDATE table_name_here SET code = code + 1 WHERE ? = ??

Replace the ? with unique identifier column name and ?? with it's value for row you are working with.

After your comment: Let's say you have previous row code value stored in $1 and actual row id in $2. Then:

UPDATE registrations SET code = $1 + 1 WHERE id = $2

After your second comment:

UPDATE registrations SET code = (SELECT MAX(code) FROM registrations) + 1 WHERE docentry = `new_row_docentry_value`

Upvotes: 1

Related Questions