On 16 March 2018 at 18:24, Robert M. Münch <robert.mue...@saphirion.com> wrote:
> Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since > it doesn’t has an UPSERT? > > So, if I have a table with 30 columns and my code updates sub-sets out of > these columns, I don’t want to write queries that manually retrieve the old > values one by one. > > insert or replace into Book (ID, Name, TypeID, Level, Seen) values ( > (select ID from Book where Name = "SearchName"), > "SearchName", > 5, > 6, > (select Seen from Book where Name = "SearchName")); > > So, for every column I don’t want to change I need to add a sub-select > statement. If I need to build this statement dynamically, IMO it would be > better to handle this code directly in code: > > if(record-exists?){ > UPDATE … > } else { > INSERT … > } > 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") 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. 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. Whether this makes a significant difference in practice I don't know :) -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users