Am Dienstag, 24. August 2004 20:53 schrieb Kurt Welgehausen:
> > there might be a bug in the replace-conflict handler of sqlite-2.8.15...
>
> What you are seeing is the correct behavior; the entire row
> is replaced. Perhaps you are confusing insert with update.
Kurt,
you would be absolutely right if I'd tried to update a single row, but I hit
this behaviour when I tried to update multiple rows simultanously.
Given are two tables:
CREATE TABLE t1(key integer primary key, value integer, other integer)
CREATE TABLE t2(key integer primary key, value integer, other integer)
I want to merge these, summing up the value-data of both tables - given they
have the same key.
Therefore, I stack the tables by UNION'ing them. Then, I compute the sums of
the values (GROUP'ed BY key), and INSERT OR REPLACE them in the original
table tbl, i.e.:
> SELECT * from t1;
1|1|7
2|1|13
> SELECT * from t2;
2|2|13
3|2|19
> SELECT key, SUM(value), other FROM (SELECT key, value, other FROM t1 UNION
ALL SELECT key, value, other FROM t2) GROUP BY key ORDER BY key;
1|1|7
2|3|13
3|2|19
And this is the result I was looking for - I just had to add the
'other'-column in my query. Stupid me.
Nevertheless, it'd vote for an addition to the manual, e.g.
"When a UNIQUE constraint violation occurs, the pre-existing rows that are
causing the constraint violation are removed prior to inserting or updating
the current row [, i.e. any data the pre-existing row holds, which is not
inserted again, is lost]."
Regards
Daniel
--
Dipl.-Math. (FH) Daniel Franke
Institut fuer Medizinische Biometrie und Statistik
Medizinische Universität zu Luebeck
Ratzeburger Allee 160, Haus 4
23538 Luebeck
Telefon: 0451-500-2786
Telefax: 0451-500-2999
[EMAIL PROTECTED]
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
