[EMAIL PROTECTED] wrote:
Isaac Raway <[EMAIL PROTECTED]> wrote:
[I]nstead of dropping the row existing, [the REPLACE algorithm should]
simply update the provided fields in place, leaving the unmodified fields
as is. I'd call this behavior OR UPDATE as it would be exactly equivalent
to doing an UPDATE on the existing row(s).
There might be two or more rows that conflict. How would it choose
which row to use as the basis?
CREATE TABLE t1(a UNIQUE, b UNIQUE, c DEFAULT 3);
INSERT INTO t1 VALUES(1,1,1);
INSERT INTO t1 VALUES(2,2,2);
REPLACE INTO t1(a,b) VALUES(1,2);
SELECT c FROM t1;
The statement sequence above should generate a single row of
result. The current implementation returns 3. What would you
have it return instead?
If I'm not mistaken the SELECT would actually return 3 twice:
+-+
|3|
+-+
|3|
+-+
I would have it return each row's value before the query, i.e.:
+-+
|1|
+-+
|2|
+-+
To me this makes much more sense in almost all cases that I can think
of, not the least of which is my current application.
Basically, it would be nice to be able to update a single field (or a
set of fields) in a set of rows without touching the fields not being
addressed, alternately creating a new row if no UNIQUE conflicts arise.
