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
