There are no stored procedures in SQLite. But you could try creating a trigger program. Not sure if this works...
CREATE TRIGGER BEFORE UPDATE ON <table> WHEN NOT EXISTS SELECT 1 FROM <table> WHERE <primary key> BEGIN INSERT INTO <table> (<key fields> VALUES (NEW. ...); END; -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Peter Michaux Gesendet: Donnerstag, 22. März 2018 20:18 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
