Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

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

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

y 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 Peter Michaux >Sent: Thursday, 22 March, 2018 13:18 >To: SQLite mailing list >Subject: Re

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Thursday, March 22, 2018, 7:18:08 PM, Peter Michaux 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

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

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux 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 >

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

f Peter Michaux > >Sent: Friday, 16 March, 2018 12:42 > >To: SQLite mailing list > >Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE > >followed by INSERT > > > >Thank you for the information, Keith. > > > >It comes as a surprise to m

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

gt; >> --- >> 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

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

] On Behalf Of Peter Michaux Sent: Friday, March 16, 2018 2:42 PM To: SQLite mailing list Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT Thank you for the information, Keith. It comes as a surprise to me that the conflict resolution clause of the statement

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

gt;Sent: Monday, 12 March, 2018 21:09 > >To: sqlite-users@mailinglists.sqlite.org > >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE > >followed by INSERT > > > >Hi, > > > >I have read that INSERT OR REPLACE is equivalent to a DELETE followe

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

qlite.org] On Behalf Of Peter Michaux >Sent: Monday, 12 March, 2018 21:09 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE >followed by INSERT > >Hi, > >I have read that INSERT OR REPLACE is equivalent to a DELET

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

t;Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE >followed by INSERT > >Hi, > >I have read that INSERT OR REPLACE is equivalent to a DELETE followed >by an >INSERT. I came across a case where that is not true. > >Set up a test case > >$ rm -f asdf

[sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Hi, I have read that INSERT OR REPLACE is equivalent to a DELETE followed by an INSERT. I came across a case where that is not true. Set up a test case $ rm -f asdf.sqlite && sqlite3 asdf.sqlite sqlite> .mode columns sqlite> .headers on sqlite> PRAGMA foreign_keys=OFF;