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
