I would suggest using the PostgreSQL way:
https://www.postgresql.org/docs/9.5/static/sql-insert.html
INSERT INTO ...
ON CONFLICT [(<column name>)] DO UPDATE
SET foo = ... , bar = ... ;
This approach is really cool, because we can specify which key is more
important and discard other conflicts as an error. For example, given
the following table:
CREATE TABLE foo(
id INTEGER NOT NULL,
foo_key TEXT NOT NULL,
some_data TEXT,
PRIMARY KEY(id),
UNIQUE (foo_key)
);
INSERT INTO foo(id, foo_key, some_data) VALUES(1, "XXX", "...");
INSERT INTO foo(id, foo_key, some_data) VALUES(2, "YYY", "...");
If we are performing a query:
INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
<OR UPDATE ...>
Which record should we update and what columns?
Having the ability to specify a specific column on which the conflict
is actually an acceptable event lets the developer to make a decision
how to resolve it:
INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
<ON CONFLICT (foo_key) DO UPDATE SET some_data = "...">
19 March 2018, 18:41:34, by "R Smith" <[email protected]>:
> 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
> [email protected]http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
