From: Benjamin Pflugmann
Date: October 2 2000 7:30am
Subject: Re: REPLACE
List-Archive: http://lists.mysql.com/internals/14
Message-Id: <20001002093058.H30777@spin.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi.

You may not use 'number+1' within REPLACE, specifically, you may not
use any column name. It becomes more obvious, if you consider that
REPLACE works like DELETE+INSERT:

You neither may usse something like INSERT INTO foo VALUES (blah+1)
Which value should 'blah' have?

I don't know, however, why MySQL doesn't produces some kind of error
instead of using some undefined value (which happens to coincide
sometimes with the value you expected).

Bye,

        Benjamin.

On Mon, Sep 25, 2000 at 04:50:10PM +0200, you wrote
[...]
> 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 ?