On 2016/06/30 10:54 AM, Olivier Mascia wrote:
Does the "INSERT OR REPLACE" syntax not provide what you are looking
for?
Absolutely not. "OR REPLACE", as Ryan and Simon pointed out clearly, is a
conflict resolution mechanism.
1) The insert would have to trigger a constraint violation to execute the
replace.
2) The replace *do* delete the row and insert a new one: this would have effect
on related data (on delete cascade for instance).
3) The typical use case for some form of "UPSERT" (whatever the name and syntax
it takes) is to generally update a row, assumed to be existing, inserting it at that
occasion if needed.
This is true, but the UPSERT is a lofty beast. May I remind people of
the intense bulk of SQL you have to type to make it happen in MSSQL and
the like? here is a minimal set:
MERGE dbo.xxx AS T
USING dbo.yyy AS S
ON T.SomeID = S.SomeID
WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever
checking is relevant
THEN UPDATE SET T.ValueThatNeedsChanging = NewValue
WHEN NOT MATCHED
THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
VALUES () / SELECT clauses
;
That's twice the code needed to do the things others suggested (INSERT
IGNORE followed by UPDATE) - and no, I'm not allergic to typing code,
but can you imagine the SQL engine changes that would be required to
parse and understand all that and describe it in the current
prepared-statement structure? I believe that would take a good ol' bite
out of the "Lite" claim.
This is not to say it shouldn't be done, nor an excuse for it not to be
done (I'm merely pointing out why it mightn't have been done yet, as
some asked that question), but I think the weight of the decision would
be Gain vs. Effort / Size increase / Processing increase (in parser terms).
If it can help 1% of queries to run a little bit faster during a job
which is already blisteringly fast - is it really worth the effort?.
Granted, 1% of all SQlite queries in the World running more efficiently
would probably amount to measurable global energy saving, but then 1% is
probably overestimating by a lot.
Someone mentioned having to climb an Index twice - fair enough, but most
queries do this a zillion times for look-ups, it's hardly a chore,
that's why we have indices.
Again, no reason not to do it, but I would schedule the implementation
of MERGE right after adding Checksums to pages. :)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
