ehh ehh - 1 month ago 9
SQL Question

Increment an ID within an INSERT INTO statement in AS400

Is there a way to insert a new record to a table that has an non-unique id and set this id to the next number in the same SQL statement?

Something like

INSERT INTO T1 (id, fname, lname)
VALUES ([last id + 1], 'Dan', 'Thomson');

Answer

this probably works

INSERT INTO T1 (id, fname, lname)
VALUES (ifnull((select max(id) from T1),0) + 1, 'Dan', 'Thomson')