On 16 March 2018 at 18:24, Robert M. Münch <[email protected]>
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
