Peter, is "INSTEAD OF" trigger not available on your version of SQLite?
https://sqlite.org/lang_createtrigger.html#instead_of_trigger CREATE VIEW mytable_UPSERT AS SELECT * FROM mytable; CREATE TRIGGER mytable_UPSERT INSTEAD OF INSERT ON mytable_UPSERT BEGIN -->INSERT OR IGNORE ... ; -->UPDATE .... ; END; INSERT INTO mytable_UPSERT .... Peter On Thu, Mar 22, 2018 at 12:18 PM, Peter Michaux <petermich...@gmail.com> 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. > > 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? > > 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. > > Thanks. > > Peter > > > Peter > > > > On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt <k.n...@zonnet.nl> wrote: > > > On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux > > <petermich...@gmail.com> wrote: > > > > > You are right that my purpose does seem kind of confusing. > > > > > > What I really want is UPSERT to avoid doing an UPDATE, checking if the > > > number of rows affected is zero, then doing an INSERT. The lack of > UPSERT > > > leads to a lot more application code using the UPDATE/INSERT > combination. > > > UPSERT doesn't exist in SQLite so I was trying to work around that by > > using > > > INSERT OR REPLACE which is not the same thing. I can see from another > > > recent thread that some others also think that UPSERT would be a > valuable > > > addition to SQLite. > > > > I fail to see the problem in > > BEGIN; > > INSERT OR IGNORE ... ; > > UPDATE .... ; > > COMMIT; > > Simple code, no need to test number of affected rows, and pretty > > fast because the relevant pages will be in cache. > > > > Or use an updatable view with an INSTEAD OF INSERT trigger. > > > > I did notice that attempts to define a proper UPSERT syntax > > opened a can of worms by itself because it (also) has to provide > > two colum lists, one for a full INSERT if the row with that PK > > doesn't exist, and another one for the columns to be updated > > when the row already exists. So, I don't see a big advantage in > > UPSERT. > > > > My humble two cents, > > > > -- > > Regards, > > Kees Nuyt > > _______________________________________________ > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
