From: Frank Lindberg
Date: September 25 2000 2:50pm
Subject: REPLACE
List-Archive: http://lists.mysql.com/internals/13
Message-Id: <71952D2721CDD31182DC0090273D3336733954@piett.int.tele.dk>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"

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