A few years back I've been asking the same question. To be honest, there's no
more
efficient alternative, than the one that can be implemented within library
itself.
Both performance-wise and productivity-wise.
Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with problems:
* Which strategy to choose, INSERT + UPDATE or the reverse? No way to make
this generic without hurting performance
* No matter the strategy, we end up with two queries which leads to extra code
that has to be maintained and kept in sync plus a penalty from preparing two
statements
* Existence of two statements leaves us vulnerable to race conditions, which
adds two extra statements to BEGIN and COMMIT a transaction
Even if for some reason we dismiss all of the said above, UPSERT scenario is
waaaay to common.
Society wise it's much more efficient to make a change in the library for a
convenience of
thousands (or millions?) of library users rather than leaving it up for them to
figure out.
Best regards,
Paul
16 March 2018, 12:25:06, by "Robert M. Münch" <robert.mue...@saphirion.com>:
> 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 …
> }
>
> Any suggestions / feedback?
>
> Viele Grüsse.
>
> --
>
> Robert M. Münch, CEO
> M: +41 79 65 11 49 6
>
> Saphirion AG
> smarter | better | faster
>
> http://www.saphirion.comhttp://www.nlpp.ch
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.orghttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
