Isaac Raway wrote:
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:
No. Remember - INSERT OR REPLACE first deletes all conflicting rows, and
then inserts the new row. In this case, you end up with just one row in
the table containing (1, 2, 3)
I would have it return each row's value before the query, i.e.:
+-+
1|
+-+
2|
+-+
So you are saying you are happy about the fact that you insert values
(1, 2), the operation succeeds, but in the end there is actually no row
in the table with the values (1, 2) ? And what would
sqlite3_last_insert_rowid return ?
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.
The easiest way to do this now is to run an UPDATE statement, check with
sqlite3_changes whether any rows actually were updated, and if not
perform an INSERT.
Igor Tandetnik
