Phani,
INSERT OR REPLACE is *close* to an UPSERT / MERGE / whatever you wanna call it,
but changes the rowid (actually, creates a whole new row, I think) on collision
with a constraint. An example to illustrate:
SQLite version 3.3.11
Enter ".help" for instructions
sqlite> .mode column
sqlite> .headers on
sqlite>
sqlite> create table t (id integer primary key autoincrement, a text, b text, c
text);
sqlite> create unique index idx1 on t (a, b, c);
sqlite>
sqlite> insert into t (a, b, c) values ('a', 'b', 'c');
sqlite> insert into t (a, b, c) values ('b', 'b', 'c');
sqlite>
sqlite> select * from t;
id a b c
---------- ---------- ---------- ----------
1 a b c
2 b b c
sqlite>
sqlite> insert or replace into t (a, b, c) values ('a', 'b', 'c');
sqlite>
sqlite> select * from t;
id a b c
---------- ---------- ---------- ----------
2 b b c
3 a b c
I needed to retain the previous rowid (1) on collision. For what it's worth,
I'm just selecting to test for existence first (I need the rowid anyway, so no
real penalty), so no problem here. There are other ways, like performing an
UPDATE WHERE a = 'a' AND b = 'b' AND c = 'c', then testing if
(sqlite3_changes() == 0), if so, INSERT the data, which is likely faster
(thanks for the idea Igor).
I think the SQL standard is going to adopt the MERGE command for this type of
operation, but I'm not sure.. is anyone in the know? It's in ORACLE now, surely
elsewhere too..
- Trey
----- Original Message -----
From: "B V, Phanisekhar" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, May 08, 2007 10:59 AM
Subject: RE: [sqlite] INSERT OR REPLACE without new rowid
Hi Trey,
Even I was looking for something like this. But I don't think SQL
allows you to do this. I didn't get what u said about INSERT OR REPLACE
looks good.
Regards,
Phani
-----Original Message-----
From: Trey Mack [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 24, 2007 9:11 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] INSERT OR REPLACE without new rowid
Hello all,
I'd like to perform an update to a row if it exists (uniquely identified
by
3 text columns), otherwise insert a new row with the right data. INSERT
OR
REPLACE looks good, but it generates a new primary key each time there
is a
conflict. If the row exists, I need to keep the original primary key
(rowid).
Any way to do this short of SELECT.. if (exists) UPDATE else INSERT ?
Thanks,
Trey
------------------------------------------------------------------------
-----
To unsubscribe, send email to [EMAIL PROTECTED]
------------------------------------------------------------------------
-----
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
