REPLACE

[Frank Lindberg]

Hi,

When i use REPLACE i can not make it work as expected. Included is an
example.

I have the following 'test' table;
	+--------+--------------+------+-----+---------+-------+
	| Field  | Type         | Null | Key | Default | Extra |
	+--------+--------------+------+-----+---------+-------+
	| name   | varchar(255) |      | PRI |         |       |
	| number | int(5)       |      |     | 0       |       |
	+--------+--------------+------+-----+---------+-------+

mysql> REPLACE INTO test VALUES ('agent', number+1);
	Query OK, 1 row affected (0.00 sec)

	mysql> select * from test;
	+-------+--------+
	| name  | number |
	+-------+--------+
	| agent |      1 |
	+-------+--------+
	1 row in set (0.00 sec)

This works fine, so i do it again:

mysql> REPLACE INTO test VALUES ('agent', number+1);
	Query OK, 2 rows affected (0.00 sec)

	mysql> select * from test;
	+-------+--------+
	| name  | number |
	+-------+--------+
	| agent |      2 |
	+-------+--------+
	1 row in set (0.00 sec)

Still works as expected

mysql> REPLACE INTO test VALUES ('agent', number+1);
	Query OK, 2 rows affected (0.00 sec)

	mysql> select * from test;
	+-------+--------+
	| name  | number |
	+-------+--------+
	| agent |      3 |
	+-------+--------+
	1 row in set (0.00 sec)

Still, but....

mysql> REPLACE INTO test VALUES ('agent2', number+1);
	Query OK, 1 row affected (0.00 sec)

	mysql> select * from test;
	+--------+--------+
	| name   | number |
	+--------+--------+
	| agent  |      3 |
	| agent2 |      4 |
	+--------+--------+
	2 rows in set (0.00 sec)

Now i had expected 'agent2' to have the value '1', but it gets the last
value from the table - and it touches 2 rows (not 1 row).

I have also tryed with number=number+1 etc. but i can not make it work in 1
statement. So i will have to use Select, Update eg., and then i loose the
point of replace - right - or am i wrong ?

/Frank