Let's say I have a table with the struture of
JOBNUMBER, TRNSNMBR,SEQNUMBR,AMOUNT and the primary key is based on
I want to be able to duplicate the record and want to use the SEQNUMBR to avoid duplicates.
INSERT INTO MYTABLE
SELECT JOBNUMBER, TRNSNMBR,SEQNUMBR
WHERE JOBNUMBER='MYJOB' AND TRNSNMBR='MYTRXNMBR' AND SEQNUMBR='MYSEQ'
Will result in a duplicate record. What is the cleanest, most efficient way to structure this insert statement so that it will find the highest value for SEQNUMBR and then increment it by 1? The possibility is there that users will want to duplicate this same record more than once so it cannot just be add 1, it needs to find the highest value and then increment by 1. I am not allowed to modify the structure of the underlying table.