Thursday, March 22, 2018, 7:18:08 PM, Peter Michaux wrote:
> I think there are a couple main offenders with
>> BEGIN;
>> INSERT OR IGNORE ... ;
>> UPDATE .... ;
>> COMMIT;
> The first is that it is bulky. If this is in the application code then it
> has to be repeated for each desired UPSERT and it has to be repeated in the
> code of each application that uses the database.
From what I can remember, most of the suggested formats for an UPSERT
command (except perhaps R. Smith's "NOT"/"KEEP" idea) involve two
lists of fields/values, so would be similarly "bulky" as separate
INSERT and UPDATE commands, and need similar amounts of application
code.
> The second is that it seems so inefficient in the case of a new row being
> inserted. The row is inserted and then immediately updated. Why do both
> operations when only one is needed?
There are at least two alternatives (hopefully I've given correct
attribution):
o "INSERT OR IGNORE" the "key" fields (ensures they now exist)
followed by an UPDATE for the remaining fields (Simon Slavin).
o "INSERT OR IGNORE" all values; if sqlite3_changes() indicates
nothing changed (because the record is already present), perform
the UPDATE (Olivier Mascia).
> Is it possible to write a stored procedure that checks a result of the
> INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
> That would at least move the bulky code out of the application and into the
> database. Also it seems it would be more efficient.
In some cases, e.g. where the data to be UPSERTed is in a (possibly
transient) table you can use a TRIGGER to perform the UPDATE part. (At
the risk of blowing my own trumpet, see an answer of mine on
StackOverflow: https://stackoverflow.com/a/22481731/2096401).
Regards,
Graham
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
