Hi,
Great! This works, I did not use the 'null' and that is where I had a problem. One other questions: is this atomic? If I have several processes trying to do this at one time, will each one correctly update the table (assuming they have the same 'name' value. Also, in the part: unique (name(100)), what does the 100 do here? Is that saying the first 100 chars are considered unique? Thanks, Cheers, Douglas > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Monday, March 10, 2003 10:36 AM > To: Douglas B Jones > Cc: [EMAIL PROTECTED] > Subject: Re: automatically incrementing an int value > > > > As I read the manual, the REPLACE command will do what you want. > > Make the name field UNIQUE, and the number field AUTO_INCREMENT NOT NULL. > Replace dos a delete-if-present, insert. The insert generates a new ID. > > See test below, and note two rows affected by second replace. > > mysql> create table test (a int auto_increment not null, name tinytext not > null, primary key (a), unique (name(100))) ; > Query OK, 0 rows affected (0.02 sec) > > mysql> replace into test values (null, "hello") ; > Query OK, 1 row affected (0.01 sec) > > mysql> select * from test ; > +---+-------+ > | a | name | > +---+-------+ > | 1 | hello | > +---+-------+ > 1 row in set (0.01 sec) > > mysql> replace into test values (null, "hello") ; > Query OK, 2 rows affected (0.02 sec) > > mysql> select * from test ; > +---+-------+ > | a | name | > +---+-------+ > | 2 | hello | > +---+-------+ > 1 row in set (0.00 sec) > > > > > > > |---------+----------------------------> > | | "Douglas B. | > | | Jones" | > | | <[EMAIL PROTECTED]>| > | | | > | | 10/03/2003 15:11 | > | | Please respond to| > | | douglas | > | | | > |---------+----------------------------> > > >----------------------------------------------------------------- > ------------------------------------------------------------------ > ---------------| > | > > | > | To: [EMAIL PROTECTED] > > | > | cc: Douglas B Jones <[EMAIL PROTECTED]> > > | > | Subject: automatically incrementing an int value > > | > > >----------------------------------------------------------------- > ------------------------------------------------------------------ > ---------------| > > > > > > Hi, > > First, the version: 3.23.49 > > If I have a table with two elements: name char(128), n int > how do I have it so that n will increment each time? > > I take it that name needs to be a primary key. How do I get > n to increment in vaue each time. This is not auto_increment. > I want name to be in the table only once, but n to increase > in value each time. 'name' might not be in there the first time, > so I can't use an update and the insert option of 'on duplicate' > is not available in this version of mysql. What would be the > table definition recommended and what would be the syntax for > a single line mysql statement to either create name the first > time of increment it. This would be run from a shell scripts, ie: > > grep somepattern file|awk '{printf "mysql statment;\n",$2}'|mysql mytable > > $2 in the awk is the 'name' field. Of course, the printf statement > would be changed to use $2 and have n increment one. Please also > note that several processes might be updating mytable at the same > time and I want each one to increment the n value. > mysql.com/ (the list archive) > > > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
