[EMAIL PROTECTED] wrote:
Isaac Raway <[EMAIL PROTECTED]> wrote:
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|
+-+
Try it.
Okay I wasn't really thinking about the implications of b also being
unique. Serves me right for not trying code before commenting on it.
Here's a more complete example that shows the problem I'm facing.
CREATE TABLE t2(id PRIMARY KEY, a, b, c, d, e);
INSERT INTO t2 VALUES(1, 1, 2, 3, 4, 5);
INSERT INTO t2 VALUES(2, 6, 7, 8, 9, 10);
SELECT * FROM t2;
1|1|2|3|4|5
2|6|7|8|9|10
INSERT OR REPLACE INTO t2(id, e) VALUES(2, 11);
SELECT * FROM t2 WHERE id = 2;
2|||||11
The result of the last query, I would think SHOULD be (or at least
should be optionally):
2|6|7|8|9|11
Granted, with the word "REPLACE" the observed behavior makes perfect
sense, but as I said I'd like to see a syntax such that
INSERT OR UPDATE INTO t2(id, e) VALUES(2, 11);
SELECT * FROM t2 WHERE id = 2;
would in fact return my expected row.
