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
