Re: [sqlite] update or replace ...

[R Smith]

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 T.ValueThatNeedsChanging = NewValue
  WHEN NOT MATCHED
THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
VALUES () / SELECT clauses
;

I, hopefully, never used such a piece of coding in my whole life and I know, 
now, why all my human body cells refrained me to ever even approach MSSQL. :)


Ha, ok, but I wasn't really complaining about the way it is done in 
MSSQL, I was merely saying what is needed for it to work, and I see now 
you mean more of a INSERT OR UPDATE the way MySQL does it. Don't 
disregard the above MERGE statement though, it is a very powerful piece 
of SQL and I quite like to use it. Very often (if not mostly) you want 
to update and/or insert only in certain cases (WHERE modified = 1) - 
taking values from a table (rather than just VALUES like I did in my 
examples) and for that this MERGE mechanism is an amazing device. It's 
basically UPSERT with expressions, filtering and sub-queries allowed.


If we /are/ going to go full regalia in SQLite, I'd push for MERGE[1] 
actually rather than just INSERT - UPDATE, but the caveats I mentioned 
earlier remain.


[1] Perhaps borrowing from the PostGres MERGE implementation rather...


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Paul Egli]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Paul Egli]

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 SET T.ValueThatNeedsChanging = NewValue
> >  WHEN NOT MATCHED
> >THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
> > VALUES () / SELECT clauses
> > ;
>
> I, hopefully, never used such a piece of coding in my whole life and I
> know, now, why all my human body cells refrained me to ever even approach
> MSSQL. :)
>
> The - useful - "upsert" which I can make good use of is the simpler one
> you can find in FirebirdSQL, where it is called UPDATE OR INSERT with
> pretty much the same syntax as an insert.
>
> Easy to read, useful and effective for what use cases it is designed for.
>
>
I've used MERGE INTO a lot and sometimes wished that SQLite had it, but i
can understand why it's not a priority. To me, the real value of MERGE is
the atomicity of the operation. You can perform inserts, updates, and
deletes all in one statement--No chance for race conditions if data changes
between the operations.

In SQLite where only a single writer is allowed at a time and the only
isolation level available Serializable, all you need to do is BEGIN
IMMEDIATE and you get the atomicity you need, even if it seems unnatural
(to me) to use two or three different statements to do what is logically
one operation.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Olivier Mascia]

> 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 that's 
> the case, the simple usual upserts in the CLI would work just dandy (and the 
> order doesn't matter much for lookups, but it is very slightly more efficient 
> to do the Update first as sometimes there's nothing to update - if the update 
> is done second, there's always something to update.

Thanks Ryan,

Speed concern while using it in scripts is indeed not my concern.  I just don't 
like to have to repeat myself twice... to my computer, especially when I have 
to tell things first using either the insert or update syntax then say it again 
using the other syntax. :) And *that* is my concern when having to write a 
piece of script for some quick data fix. :)

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Olivier Mascia]

> 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
>  WHEN NOT MATCHED
>THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
> VALUES () / SELECT clauses
> ;

I, hopefully, never used such a piece of coding in my whole life and I know, 
now, why all my human body cells refrained me to ever even approach MSSQL. :)

The - useful - "upsert" which I can make good use of is the simpler one you can 
find in FirebirdSQL, where it is called UPDATE OR INSERT with pretty much the 
same syntax as an insert.

Easy to read, useful and effective for what use cases it is designed for.

-- 
Meilleures salutations, Met vriendelijke groeten,  Best Regards,
Olivier Mascia (from mobile device), integral.be/om



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[R Smith]

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 
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 that's the case, the simple usual upserts in the 
CLI would work just dandy (and the order doesn't matter much for 
lookups, but it is very slightly more efficient to do the Update first 
as sometimes there's nothing to update - if the update is done second, 
there's always something to update.


Here is a performance measurement of a typical script that would run 
just fine in the CLi merrily doing Upserts wihout needing to check any 
operation result (i.e the slowest possible way to do it).
It's run in SQLitespeed (but will work perfectly in the CLI too) so I 
can measure the Virtual Machine operations count and compare with the 
same script but without any of the unnecessary steps that makes upserts 
(this second one has half the SQL operations and will even be much more 
efficient than a MERGE statement).


Note the results in VM Steps measured in both cases. To my mind, the 
difference is not worth fretting over if you make scripts for the CLI.
(Note: The time improvement has more to do with caching than efficiency, 
but the VM steps don't lie).



  -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on SQLitespeed 
version 2.0.2.4.


  -- Script Items: 10 Parameter Count: 0
  -- 2016-06-30 13:47:04.423  |  [Info]   Script Initialized, 
Started executing...
  -- 



CREATE TABLE t(ID INTEGER PRIMARY KEY, A, B);

INSERT INTO t(A, B) VALUES
('John' , 'Smith'),
('Jerry' , 'Jones'),
('James' , 'Smith'),
('Jimmy' , 'Jones');

SELECT * FROM t;


  --  ID  | A   | B
  --  | --- | ---
  --   1  | John| Smith
  --   2  | Jerry   | Jones
  --   3  | James   | Smith
  --   4  | Jimmy   | Jones

UPDATE t SET A='Jenny', B='Smith' WHERE ID=2;  -- UPSERT Type 
1(best)

INSERT OR IGNORE INTO t(ID,A,B) VALUES (2,'Jenny','Smith');


INSERT OR IGNORE INTO t(ID,A,B) VALUES (3,'Jenna','Jones');-- UPSERT Type 2
UPDATE t SET A='Jenna', B='Jones' WHERE ID=2;


UPDATE t SET A='J.K.', B='Johnson' WHERE ID=99; -- UPSERT Type 1 again
INSERT OR IGNORE INTO t(ID,A,B) VALUES (99,'J.K.','Johnson');


SELECT * FROM t;


  --  ID  | A   | B
  --  | --- | -
  --   1  | John| Smith
  --   2  | Jenna   | Jones
  --   3  | James   | Smith
  --   4  | Jimmy   | Jones
  --  99  | J.K.| Johnson

  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.069s
  -- Total Script Query Time: 0d 00h 00m and 
00.037s

  -- Total Database Rows Changed: 7
  -- Total Virtual-Machine Steps: 233
  -- Last executed Item Index:10
  -- Last Script Error:
  -- 






  -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on SQLitespeed 
version 2.0.2.4.


  -- Script Items: 7  Parameter Count: 0
  -- 2016-06-30 13:53:38.403  |  [Info]   Script Initialized, 
Started executing...
  -- 



CREATE TABLE t(ID INTEGER PRIMARY KEY, A, B);

INSERT INTO t(A, B) VALUES
('John' , 'Smith'),
('Jerry' , 'Jones'),
('James' , 'Smith'),
('Jimmy' , 'Jones');

SELECT * FROM t;


  --  ID  | A   | B
  --  | --- | ---
  --   1  | John| Smith
  --   2  | Jerry   | Jones
  --   3  | James   | Smith
  --   4  | Jimmy   | Jones

UPDATE t SET A='Jenny', B='Smith' WHERE ID=2;

UPDATE t SET A='Jenna', B='Jones' WHERE ID=2;

INSERT OR IGNORE INTO t(ID,A,B) VALUES (99,'J.K.','Johnson');

SELECT * FROM t;


  --  ID  | A   | B
  --  | --- | -
  --   1  | John| Smith
  --   2  | Jenna   | Jones
  --   3  | James   | Smith
  --   4  | Jimmy   | Jones
  --  99  | J.K.| Johnson

  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.031s
  -- Total Script Query Time: -- --- --- --- 
--.

  -- Total Database Rows Changed: 7
  -- Total Virtual-Machine Steps: 194
  -- Last executed Item Index:7
  -- Last Script Error:
  -- 

Re: [sqlite] update or replace ...

[R Smith]

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 execute the 
replace.
2) The replace *do* delete the row and insert a new one: this would have effect 
on related data (on delete cascade for instance).
3) The typical use case for some form of "UPSERT" (whatever the name and syntax 
it takes) is to generally update a row, assumed to be existing, inserting it at that 
occasion if needed.



This is true, but the UPSERT is a lofty beast. May I remind people of 
the intense bulk of SQL you have to type to make it happen in MSSQL and 
the like? here is a minimal set:


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
  WHEN NOT MATCHED
THEN INSERT (SomeID, ValueThatNeedsChanging, AndAllOtherColumns)
VALUES () / SELECT clauses
;

That's twice the code needed to do the things others suggested (INSERT 
IGNORE followed by UPDATE) - and no, I'm not allergic to typing code, 
but can you imagine the SQL engine changes that would be required to 
parse and understand all that and describe it in the current 
prepared-statement structure? I believe that would take a good ol' bite 
out of the "Lite" claim.


This is not to say it shouldn't be done, nor an excuse for it not to be 
done (I'm merely pointing out why it mightn't have been done yet, as 
some asked that question), but I think the weight of the decision would 
be Gain vs. Effort / Size increase / Processing increase (in parser terms).


If it can help 1% of queries to run a little bit faster during a job 
which is already blisteringly fast - is it really worth the effort?. 
Granted, 1% of all SQlite queries in the World running more efficiently 
would probably amount to measurable global energy saving, but then 1% is 
probably overestimating by a lot.
Someone mentioned having to climb an Index twice - fair enough, but most 
queries do this a zillion times for look-ups, it's hardly a chore, 
that's why we have indices.


Again, no reason not to do it, but I would schedule the implementation 
of MERGE right after adding Checksums to pages. :)



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Simon Slavin]

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.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Paul]

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 iterate B-Tree 
2 times: once for INSERT, that will fail and second time for UPDATE.  
In reverse scenario, where inserts are frequent and updates are rare, INSERT + 
UPDATE approach will have almost no overhead. 
But how would you know which scenario would take place, to adjust the order of 
calls? Especially when you write a generic Insert-Or-Update method? So, 
whatever strategy you choose (either UPDATE or INSERT is first), statistically, 
on average, you have 50% of cases where UPDATE would be efficient if performed 
first and 50% of cases where the reverse is true. 

If implemented inside the SQLite engine, overhead can be removed. It is 
possible to descend B-Tree once, either to find the row to UPDATE or a hint 
where to INSERT new row. The only thing that puzzles me is why it wasn't 
implemented years ago. 

30 June 2016, 12:01:56, by "Simon Slavin" < slav...@bigfraud.org >: 

On 30 Jun 2016, at 8:24am, Olivier Mascia < o...@integral.be > 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 commands:

1) INSERT without the REPLACE
2) UPDATE

When step 1 fails because the key values already exist you trap this and 
explicitly ignore it in your code (commented, because you're being nice to 
other programmers).  Then it's always the UPDATE which updates the fields you 
care about.

Simon.
___
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

Re: [sqlite] update or replace ...

[Olivier Mascia]

> 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 in complex 
programming where you have thousands of more important things to care for, in 
addition to handle such double statement where they make sense:

>> 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. Though in larger programming tasks that ends up being 
>> rather tedious for business logic programmers. We hide this inside our C++ 
>> shell around SQLite C API and the solution is not bad but had some 
>> challenges for handling parameters to the statement(s) without risking them 
>> to be evaluated twice when re-using them for two distinct SQLite statements 
>> (while there is only one 'upsert' at the C++ logical level).


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?

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Olivier Mascia]

> 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 two commands:
> 
> 1) INSERT without the REPLACE
> 2) UPDATE
> 
> When step 1 fails because the key values already exist you trap this and 
> explicitly ignore it in your code (commented, because you're being nice to 
> other programmers).  Then it's always the UPDATE which updates the fields you 
> care about.

So
INSERT OR IGNORE ...
followed by
UPDATE ...

The (possibly) annoying thing behind this is that the most logical use case of 
an "UPDATE OR INSERT"/"UPSERT"/whatever-name is to update a row and in the rare 
case it might not already exist, to insert it.

I have not yet deep enough knowledge of inner details and optimizations inside 
sqlite.c but I tend to think it might be more costly to attempt insert first, 
having it fail (ignored) most of the times, and then only do the update.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Clemens Ladisch]

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 this is to do two commands:
>
> 1) INSERT without the REPLACE
> 2) UPDATE
>
> When step 1 fails because the key values already exist you trap this
> and explicitly ignore it in your code (commented, because you're being
> nice to other programmers).

Trapping only the conflict violation while properly handling any other
errors makes this even more complex.

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.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Simon Slavin]

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 commands:

1) INSERT without the REPLACE
2) UPDATE

When step 1 fails because the key values already exist you trap this and 
explicitly ignore it in your code (commented, because you're being nice to 
other programmers).  Then it's always the UPDATE which updates the fields you 
care about.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Olivier Mascia]

> 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 REPLACE", as Ryan and Simon pointed out clearly, is a 
conflict resolution mechanism.

1) The insert would have to trigger a constraint violation to execute the 
replace.
2) The replace *do* delete the row and insert a new one: this would have effect 
on related data (on delete cascade for instance).
3) The typical use case for some form of "UPSERT" (whatever the name and syntax 
it takes) is to generally update a row, assumed to be existing, inserting it at 
that occasion if needed.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[nomad]

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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Olivier Mascia]

> 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 
REPLACE|FAIL|IGNORE|... in SQLite is related to the ON CONFLICT clause.

I'd love to have some equivalent to the UPDATE OR INSERT statement (or 
variation on it) that some other engines expose.  But clearly building it on 
top of a syntax using OR wouldn't be a nice idea: it would bring multiple 
interpretations to the 'OR' in this context, sometimes ON CONFLICT resolution 
and sometimes 'ON NOTHING DONE' resolution.

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. Though in larger programming tasks that ends up being rather 
tedious for business logic programmers. We hide this inside our C++ shell 
around SQLite C API and the solution is not bad but had some challenges for 
handling parameters to the statement(s) without risking them to be evaluated 
twice when re-using them for two distinct SQLite statements (while there is 
only one 'upsert' at the C++ logical level).

All in all, I hope SQLite could bring up some sort of such UPDATE OR INSERT 
(which would be more easily named UPSERT in SQLite existing syntax), one day.  
Does adding such a new statement would fit the 'small' within "Small. Fast. 
Reliable. Choose any three." I'm not sure, I don't yet have enough background 
with it.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[Simon Slavin]

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 nothing.
> Does this fit the intended behavior?
> Would that mean 'or replace' in the context of an update statement is a void 
> operation?

For the UPDATE OR REPLACE command, the REPLACE part comes into play only if an 
UPDATE would cause some sort of conflict.  Since the UPDATE command does 
nothing in this case, there will be no conflict, so there will be no REPLACE.

This command will return SQLITE_OK.  It is an entirely legal command which does 
nothing in this situation.

Under different circumstances (i.e. if the table already held data) the same 
command might result in a REPLACE.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update or replace ...

[R Smith]

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 the intended behavior?
Would that mean 'or replace' in the context of an update statement is a void 
operation?


...OR REPLACE here indicates a Conflict-resolution algorithm. Anything 
after the OR in an UPDATE OR xxx  statement relates to the conflict 
resolution algorithm - i.e. What must be done if this change causes a 
constraint to fail?


In all the next examples, assume two rows with Primary key IDs 1 and 2 
already exist in the Database:


UPDATE OR FAIL   SET ID = 1 WHERE ID = 2;
 - indicates that the executions should stop immediately and produce a 
Constraint-failed error without touching any transaction mechanics.


UPDATE OR ROLLBACK   SET ID = 1 WHERE ID = 2;
 - indicates that the executions should stop immediately and roll back 
the entire active transaction.


UPDATE OR ABORT   SET ID = 1 WHERE ID = 2;
 - indicates that the executions should stop immediately and roll back 
the current statement, but leave prior changes in the transaction as is.


UPDATE OR IGNORE   SET ID = 1 WHERE ID = 2;
 - indicates that the the update should be skipped. It's the "Do 
nothing" check.


UPDATE OR REPLACE   SET ID = 1 WHERE ID = 2;
 - indicates that the Row with ID: 1 which already exists must be 
deleted and then this row's ID should be set to 1. Dangerous!



Hope that clears it up,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] update or replace ...

[Olivier Mascia]

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 replace' in the context of an update statement is a void 
operation?

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Joe Wilson]

--- [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 TABLE t1(x UNIQUE, y);
>INSERT INTO t1 VALUES(1,2);
>INSERT INTO t1 VALUES(3,4);
>
>UPDATE OR REPLACE t1 SET x=3 WHERE y=2;
>SELECT * FROM t1;

Thanks. That's quite useful, actually. 
I used to do a DELETE followed by an INSERT in this situation.
The UDPATE OR REPLACE construct is more efficient.

Does anyone know whether UPDATE OR REPLACE is portable to any other 
popular database? (Oracle, SQL Server, MySQL, Postgres)
REPLACE() seems to be a string function in other databases.


 

Yahoo! Music Unlimited
Access over 1 million songs.
http://music.yahoo.com/unlimited

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[drh]

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 VALUES(1,2);
   INSERT INTO t1 VALUES(3,4);
   
   UPDATE OR REPLACE t1 SET x=3 WHERE y=2;
   SELECT * FROM t1;
   
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] UPDATE OR REPLACE same as UPDATE?

[Joe Wilson]

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

-
To unsubscribe, send email to [EMAIL PROTECTED]
-