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