rwheeler23
asked on
SQL Syntax to prevent duplicates
Let's say I have a table with the struture of
JOBNUMBER, TRNSNMBR,SEQNUMBR,AMOUNT and the primary key is based on
JOBNUMBER, TRNSNMBR,SEQNUMBR
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
FROM MYTABLE
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.
JOBNUMBER, TRNSNMBR,SEQNUMBR,AMOUNT and the primary key is based on
JOBNUMBER, TRNSNMBR,SEQNUMBR
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
FROM MYTABLE
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, brought the two of these together and it works like a charm. Thank you.
ASKER