Re: Auto-increment record counter on INSERT

This would cause a conflict if 2 users access that program at the same time. I would suggest to change column data type to be SERIAL and then let the server do rest of it. Although I am not aware of any major issues related with SERIALs in Informix but do check it out with Informix support. Vivek Chaudhary ______________________________ Reply Separator _________________________________ Subject: Re: Auto-increment record counter on INSERT Author: chuck.renaud (chuck.renaud@informix.com) at internet Date: 5/18/00 8:46 PM How exactly are you running this? You might do the SELECT prior to the insert and just use a variable to hold the MAX(t_srno)+1 value. Otherwise you can try INSERT INTO tltcom200500 (i_item, t_pror, t_cmnt, t_srno) SELECT "Desc", 2, "Comment", (MAX(t_srno)+1) FROMtltcom200500; I have no idea if that'll work, but it's probably the first thing I'd try off the top of my head. Can I ask why you're inserting directly into a Baan table and not using the interface? I'm not sure sure what the tltcom200 table does, but you risk RI problems by poking the database directly... > We have a system that I'd like to update with a single INSERT statement > that has a 'key' (not a real index but unique) that I want to > auto-increment. I've done the following but I get a syntax error on the > select max() part: > > INSERT INTO tltcom200500 (t_item, t_pror, t_cmnt, t_srno) > VALUES ( "Desc", 2, "Comment", select max(t_srno) + 1 from> tltcom200500); > > I know the select max() part works because I did it by itself and it > returned the value I wanted. Can anyone tell me what I'm doing wrong, > or a better way to do it? > > Thanks! > > Sam > >