If you have multiple candidate keys for a single row that match more than one
row (or the alternate candidate keys match different rows), your application
should explode immediately!
There is no need to "decide" which row is the correct one to update, you are
already in a fatal error situation and need to revisit your database design (it
is probably insufficiently normalized) and already self-inconsistent and
suffering update anomalies (or you are treating a pseudo-key as a candidate
key, which for the purposes of UPDATE or INSERT it is not -- the rowid is a
pseudo-key -- (one of/any of) the "other" candidate keys in the row is the true
primary key).
CREATE TABLE foo
(
id integer primary key,
foo_key text not null unique,
some_data blob
);
SAVEPOINT UpdateFoo;
UPDATE foo
SET some_data = :some_data
WHERE foo_key = :foo_key;
INSERT OR
IGNORE INTO foo (foo_key, some_data)
VALUES (:foo_key, :some_data);
SELECT id
FROM foo
WHERE foo_key = :foo_key;
RELEASE UpdateFoo;
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Paul
>Sent: Monday, 19 March, 2018 11:08
>To: SQLite mailing list
>Subject: Re: [sqlite] UPSERT
>
>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" <ryansmit...@gmail.com>:
>
>> 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.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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
