Neville Franks wrote:
> I want to insert a row if its key clm doesn't exist otherwise update
> it. I can search for the row and then do either an insert or update
> accordingly. However I was wondering whether the SQLite Conflict
> Resolution: INSERT OR REPLACE would be more efficient (faster). The
> problem is the REPLACE deletes the existing row and INSERT adds a new
> one, loosing the ROWID value of the original row, which I need to
> keep.
> 
> So my question is should I just forget this and do it the: select ->
> if not found insert otherwise update way or is there a way to maintain
> the original rowid using INSERT OR REPLACE?
> 
> If not what is the fastest way to check if a row exists, assuming the
> search is on a single clm which is indexed. ex.
> select myclm from mytable where myclm='abc';
> select count(*) from mytable where myclm='abc';
> add limit 1 to either of the above etc.
> 

Neville,

If you need to maintain the rowid (because it is linked to other tables) 
  then the select and insert or update method is required.

The best approach is to select the rowid of the row to be updated, so 
that it can be used directly in the update statement if required. If the 
row does not exist you will get a null value back and you must do an 
insert. If the column you are searching has a unique index then there 
can only be one result row, so there is no need to use a limit 1 clause. 
In pseudo code you do this:

s = prepare(select rowid from mytable where myclm = 'abc')
rc = step(s)
if (rc == SQLITE_ROW)
     row = column_int(s, 1)
     execute(update mytable set ... where rowid = row)
else
     execute(insert into mytable ...)

Because SQLite has an index data optimization the first query will only 
scan the index on mytable, since it can get the required rowid value 
directly from the index. Also, since you already have the rowid for the 
row in the update case, the update will not need to reference the index 
to locate the row, it will go directly to the row in the table using the 
rowid.

HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to