On 16 Mar 2018, at 11:41, Rowan Worth wrote: > Doing it with an if means you always run two queries - the first to > determine whether a row exists: > > SELECT EXISTS (SELECT ID from Book where Name = "SearchName")
Hi, yes that’s true. Not critical in my case but if you have a slow FFI in between this is not ideal. > There's two approaches which reduce the best case to a single query: > > 1. Immediately try to INSERT using the ON ABORT conflict strategy (ie.the > default). If the query fails with SQLITE_CONSTRAINT you know the row is > already present, so run the UPDATE. Yes. In my case we crash hard on any SQLite problems as such a constraint violation shouldn’t happen at all and if indicates an invalid application state. > 2. Immediately try to UPDATE the existing row. Then call sqlite3_changes to > determine how many rows were updated - if zero then you know the row didn't > exist, so run the INSERT. That’s a nice one. I’m going to try this. At least you don’t run two queries but you need two calls, replacing the query for the #of-rows-changed query, which should be quicker. Viele Grüsse. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch
signature.asc
Description: OpenPGP digital signature
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
