Incrementing sequence number
OK, you've all come across this scenario I'm sure, so can someone save me having to reinvent this particular wheel, please? You've got this table which stores, among other things, a name and a sequence number within name: Annie 1 Annie 2 Annie 3 Bob 1 Bob 2 Carol 1 Carol 2 Carol 3 etc Now I need to insert a new row for 'Annie'. I need to select the maximum sequence number currently in use for this name, increment it, and insert my new row with the newly incremented sequence number. How the devil do I do this from ESQLC in an atomic fashion, ie. how do I ensure that no-one reads the max sequence after I have read it but before I have inserted my new row? Sent via Deja.com http://www.deja.com/ Before you buy.
I would probably put a unique index on (name, sequence). That way if you do happen to get a dup, the second will error and you can find the max again. I almost said something about using a trigger, but I just remembered that I think there's some constriction on using triggers to modify records in the table that the trigger is on. Something about a trigger executing a statement that would cause the trigger to execute again, causing an infinite loop. > OK, you've all come across this scenario I'm sure, so can someone save > me having to reinvent this particular wheel, please? > > You've got this table which stores, among other things, a name and a > sequence number within name: > > Annie 1 > Annie 2 > Annie 3 > Bob 1 > Bob 2 > Carol 1 > Carol 2 > Carol 3 > etc > > Now I need to insert a new row for 'Annie'. I need to select the > maximum sequence number currently in use for this name, increment it, > and insert my new row with the newly incremented sequence number. > > How the devil do I do this from ESQLC in an atomic fashion, ie. how do > I ensure that no-one reads the max sequence after I have read it but > before I have inserted my new row? > > Sent via Deja.com http://www.deja.com/ > Before you buy. > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
Oh, yes, one other option. If you don't really care what the actual value of that sequence number is, you just want the first one to be the smallest, followed by a larger number, etc. You could just make it a regular serial column. You would end up with something like this: Annie 1 Annie 5 Annie 6 Bob 2 Bob 4 Carol 3 Carol 7 Carol 8 For Annie, this would mean that record 1 was the first, 5 second, 6 third. For Carol the sequence would be 3, 7, 8. I hope I made sense here... > OK, you've all come across this scenario I'm sure, so can someone save > me having to reinvent this particular wheel, please? > > You've got this table which stores, among other things, a name and a > sequence number within name: > > Annie 1 > Annie 2 > Annie 3 > Bob 1 > Bob 2 > Carol 1 > Carol 2 > Carol 3 > etc > > Now I need to insert a new row for 'Annie'. I need to select the > maximum sequence number currently in use for this name, increment it, > and insert my new row with the newly incremented sequence number. > > How the devil do I do this from ESQLC in an atomic fashion, ie. how do > I ensure that no-one reads the max sequence after I have read it but > before I have inserted my new row? > > Sent via Deja.com http://www.deja.com/ > Before you buy. > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
>Subject: Incrementing sequence number >From: Bytebrothers (UK) kwillis@dial.pipex.com >Date: 19.04.00 16:44 W. Europe Daylight Time >Message-id: <8dkgp1$kbc$1@nnrp1.deja.com> > > > >OK, you've all come across this scenario I'm sure, so can someone save >me having to reinvent this particular wheel, please? > >You've got this table which stores, among other things, a name and a >sequence number within name: > >Annie 1 >Annie 2 >Annie 3 >Bob 1 >Bob 2 >Carol 1 >Carol 2 >Carol 3 >etc > >Now I need to insert a new row for 'Annie'. I need to select the >maximum sequence number currently in use for this name, increment it, >and insert my new row with the newly incremented sequence number. > >How the devil do I do this from ESQLC in an atomic fashion, ie. how do >I ensure that no-one reads the max sequence after I have read it but >before I have inserted my new row? > > >Sent via Deja.com http://www.deja.com/ >Before you buy. > > > try an update cursor with a filter that locks only the tuples belonging to the sequence to be changed. Nona