Re: [sqlite] update or replace ...

On 2016/06/30 2:48 PM, Olivier Mascia wrote: Le 30 juin 2016 à 13:34, R Smith a écrit : MERGE dbo.xxx AS T USING dbo.yyy AS S ON T.SomeID = S.SomeID WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever checking is relevant THEN UPDATE SET

Re: [sqlite] update or replace ...

On Thu, Jun 30, 2016 at 8:17 AM, Paul Egli wrote: > No chance for race conditions if data changes between the operations. > I should say "if data changes *or a read happens* between the operations. :-) ___ sqlite-users mailing list

Re: [sqlite] update or replace ...

On Thu, Jun 30, 2016 at 7:48 AM, Olivier Mascia wrote: > > Le 30 juin 2016 à 13:34, R Smith a écrit : > > > > MERGE dbo.xxx AS T > > USING dbo.yyy AS S > > ON T.SomeID = S.SomeID > > WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever > checking is relevant > > THEN UPDATE

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 14:13, R Smith a écrit : > > There is no way to catch the outcome in the CLI that I know of, but that > doesn't matter. if you are writing scripts for the CLi and not programming > it, you can't possibly have SPEED as a paramount consideration, and if

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 13:34, R Smith a écrit : > > MERGE dbo.xxx AS T > USING dbo.yyy AS S > ON T.SomeID = S.SomeID > WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever checking > is relevant > THEN UPDATE SET T.ValueThatNeedsChanging = NewValue >

Re: [sqlite] update or replace ...

On 2016/06/30 12:00 PM, Olivier Mascia wrote: Besides, if you ever have to write a scripts for the command line tool, you're stuck trying to emulate that semantic. Or can we check by script the outcome of the UPDATE? There is no way to catch the outcome in the CLI that I know of, but that

Re: [sqlite] update or replace ...

On 2016/06/30 10:54 AM, Olivier Mascia wrote: Does the "INSERT OR REPLACE" syntax not provide what you are looking for? Absolutely not. "OR REPLACE", as Ryan and Simon pointed out clearly, is a conflict resolution mechanism. 1) The insert would have to trigger a constraint violation to

Re: [sqlite] update or replace ...

On 30 Jun 2016, at 10:51am, Olivier Mascia wrote: > INSERT OR IGNORE ... > followed by > UPDATE ... Of course. That's what I actually did when I did it, but I'd forgotten the best way. Your way means you don't have to check any error codes. Thanks for reminding me.

Re: [sqlite] update or replace ...

Whatever the case, it will be, on average, 1.5x time less efficient than a 'I-Hope-Would-Be' UPSERT. It all depends on the probability of INSERT to fail.  In the scenario, where updates are frequent and inserts are rare, INSERT + UPDATE approach takes ~2x performance overhead. In most cases we

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 11:17, Clemens Ladisch a écrit : > > A better way is to try the UPDATE first, and if the data was not found > (number of affected rows is zero), do the INSERT. Doesn't even need > a comment. Indeed. That is precisely what we do. And what is not so easy

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 11:01, Simon Slavin a écrit : > >> Of course writing straight code in C/C++ it's rather simple to emulate >> situations where you want to update some values in an existing row, creating >> the row if needed. > > The standard way of doing this is to do

Re: [sqlite] update or replace ...

Simon Slavin wrote: > On 30 Jun 2016, at 8:24am, Olivier Mascia wrote: >> Of course writing straight code in C/C++ it's rather simple to emulate >> situations where you want to update some values in an existing row, >> creating the row if needed. > > The standard way of doing

Re: [sqlite] update or replace ...

On 30 Jun 2016, at 8:24am, Olivier Mascia wrote: > Of course writing straight code in C/C++ it's rather simple to emulate > situations where you want to update some values in an existing row, creating > the row if needed. The standard way of doing this is to do two

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 10:06, no...@null.net a écrit : > >> I'd love to have some equivalent to the UPDATE OR INSERT statement >> (or variation on it) that some other engines expose. But clearly > > Does the "INSERT OR REPLACE" syntax not provide what you are looking > for? Absolutely not. "OR

Re: [sqlite] update or replace ...

On Thu Jun 30, 2016 at 09:24:36AM +0200, Olivier Mascia wrote: > I'd love to have some equivalent to the UPDATE OR INSERT statement > (or variation on it) that some other engines expose. But clearly Does the "INSERT OR REPLACE" syntax not provide what you are looking for? -- Mark Lawrence

Re: [sqlite] update or replace ...

> Le 29 juin 2016 à 18:56, Olivier Mascia a écrit : > > Clearly it does nothing. > Does this fit the intended behavior? > Would that mean 'or replace' in the context of an update statement is a void > operation? Thanks Ryan and Simon for your answers. Indeed I forgot the OR

Re: [sqlite] update or replace ...

On 29 Jun 2016, at 5:56pm, Olivier Mascia wrote: > What's the expected behavior of statement "update or replace ..."? > (http://sqlite.org/lang_update.html) > > create table T(K integer primary key, V text); > update or replace T set V='data' where K=1; > > Clearly it does

Re: [sqlite] update or replace ...

On 2016/06/29 6:56 PM, Olivier Mascia wrote: Dear all, What's the expected behavior of statement "update or replace ..."? (http://sqlite.org/lang_update.html) create table T(K integer primary key, V text); update or replace T set V='data' where K=1; Clearly it does nothing. Does this fit

[sqlite] update or replace ...

Dear all, What's the expected behavior of statement "update or replace ..."? (http://sqlite.org/lang_update.html) create table T(K integer primary key, V text); update or replace T set V='data' where K=1; Clearly it does nothing. Does this fit the intended behavior? Would that mean 'or

Re: [sqlite] UPDATE OR REPLACE same as UPDATE?

--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > Is UPDATE OR REPLACE always equivalent to just UPDATE? > > No. UPDATE is the same as UPDATE OR ABORT. Try replacing > the UPDATE OR REPLACE in the following script with just > UPDATE to see the difference: > >CREATE

Re: [sqlite] UPDATE OR REPLACE same as UPDATE?

Joe Wilson <[EMAIL PROTECTED]> wrote: > Is UPDATE OR REPLACE always equivalent to just UPDATE? > No. UPDATE is the same as UPDATE OR ABORT. Try replacing the UPDATE OR REPLACE in the following script with just UPDATE to see the difference: CREATE TABLE t1(x UNIQUE, y); INSERT INTO t1

[sqlite] UPDATE OR REPLACE same as UPDATE?

Is UPDATE OR REPLACE always equivalent to just UPDATE? Food fight? Enjoy some healthy debate in the Yahoo! Answers Food & Drink Q http://answers.yahoo.com/dir/?link=list=396545367