On 2018/03/19 1:50 PM, Olivier Mascia wrote:
I don't know what any 'standard' SQL defines about this.
I know that FirebirdSQL (where I came from, before meeting SQLite) did/does it
this way:
UPDATE OR INSERT INTO
{tablename | viewname} [(<columns>)]
VALUES (<values>)
[MATCHING (<columns>)]
[RETURNING <values> [INTO <variables>]]
Quite right, and the statement in MSSQL is even more convoluted, which,
if it was in SQLite like this, would require a dynamically created SQL
statement that is worse than simply computing an UPDATE and an INSERT -
which a previous poster already lamented.
My suggestion for UPSERT would be the very simple already SQLite-like
syntax of:
INSERT OR UPDATE INTO t (k1, k2, ... , kn, f1, f2, ... , fn)
followed by the usual VALUES clause or SELECT query.
Any record found to exist with the exact same value in the Primary Key
field(s) [ k1 .. kn ] has all other fields (that are NOT Primary Key
fields) updated to the new values, and if no such record is found, the
row simply gets inserted. If the inserted row OR updated values cause
any other constraint to break, then FAIL hard, the same way (and
possibly with the same ON CONFLICT options) as any other single INSERT
or UPDATE would be subjected to.
This is far better than INSERT OR REPLACE since there is no delete, and
no multiple-row delete on constraint violations.
It is simple in terms of converting any current INSERT OR REPLACE query
to an INSERT OR UPDATE query requires changing 1 word only.
Triggers should fire for ON INSERT and ON UPDATE according to whatever
actually is required during the operation.
Adding this has no backward compatibility to break, this did not exist
before and it is not schema-specific.
One possible added refinement might be an optional second field-group
that should be ignored over-and-above the PK fields during the UPDATE.
(During the INSERT of course all fields MUST be added).
2 ways this can be done easily:
A - Use a separate 2nd prototype group for Non-Updating fields, Like
this perhaps:
INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) NOT (f1, f2) VALUES
(...); -- This example updates only f3 and f4 if the record already exists.
I just picked "NOT" as the separator, perhaps "KEEP" gives better
clarity (see next example), but any good word would do.
Primary key fields pk1 and pk2 along with specified non-updating fields
f1 and f2 are all ignored during an update, but still used during an
insert.
Adding a PK field to the second set is a no-op as some might like it for
legibility. i.e this next query is equivalent to the above:
INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) KEEP (pk1, pk2, f1,
f2) VALUES (...); -- This example updates only f3 and f4, same as above.
B - Use a Marker of sorts for Non-Updating fields, Like this perhaps
using the Exclamation mark:
INSERT OR UPDATE INTO t (pk1, !pk2, !f1, !f2, f3, f4) VALUES (...); --
Again update only f3 and f4 if the record already exists.
(Adding the marker to a PK field is a no-op).
Escaping is not needed since a fieldname starting with the same marker
will be in the list of field-names, no ambiguity, and in the case where
a set of fields contain fields starting with both one and two markers
(for which the programmer should be shot, but let's assume it possible)
then the field can simply be enclosed in quotes as is the norm for
disambiguation in SQLite. This next example has fields named !f and !!f:
INSERT OR UPDATE INTO t (pk1, pk2, !"!f", !!f) VALUES (...); -- Here
updating only !!f if the record already exists.
Personally, I'm partial to option A.
I know it's a bit of work, but it seems less so than many of the other
additions - perhaps let's first have another show-of-hands to see if
this a real need, but it is asked for here more frequently than any
other feature (to my perception at least).
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
