Re: [sqlite] UPDATE question

[Peter Haworth]

I do need to maintain the uniqueness of those two columns.  I suppose I
could drop the UNIQUE constraint an check for uniqueness in my code but I'm
a great believer in having sqlite do as much as possible for me.

The "Blue" vs "blue" issue is addressed by COLLATE NOCASE (at least I think
it is - I should test it!).  The continuity of the sequence column is
handled in my application code since I couldn't think of a way to have
sqlite do it for me.

I'm still somewhat surprised that creating a unique index with columns
sorted descending, coupled with an UPDATE naming that index still resulted
in a unique constraint violation. I guess sorting the columns in an index
serves a purpose other than affecting the order in which rows are processed.

Thanks to everyone for the clever suggestions on how to do this.  I guess I
will have to decide if any of them are any "better" than what I'm doing now
which is selecting the rows whose sequence to be changed in desc order of
sequence than updating each one in a loop in my application code.


On Sat, Sep 7, 2013 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:

> Message: 23
> Date: Fri, 6 Sep 2013 17:45:59 -0400 (EDT)
> From: j.merr...@enlyton.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] UPDATE question
> Message-ID: <1378503959.951430...@apps.rackspace.com>
> Content-Type: text/plain;charset=UTF-8
>
> I propose that you remove the unique index because SQLite does not handle
> the update case the way you want. (I'd call that a bug, but sometimes
> "liteness" means that Dr Hipp can say "I'm not going to fix it" and we have
> to respect his decision.)
>
> Is there a reason other than "if you have a particular kind of bug in your
> code, you could end up violating the [Name/Sequence is unique] rule" to
> keep the index, when its presence causes you trouble? (Perhaps the answer
> is "users edit this table manually using other software" so you need the
> index to keep them from screwing up. But I doubt it, or they'd have
> complained what a pain it is to add a new row in the middle!)
>
> There is little to prevent you from having other bugs that might be
> equally bad --
>
> - putting both "Blue" and "blue" in the Name column (with separate sets of
> Sequence values) when both values shouldn't be there because the business
> context says they're the same
> - have Sequence values not starting at 1 (e.g. 2 3 4) for a particular
> Name -- perhaps that wouldn't cause any trouble in other logic, but it
> probably would
>
> If you wanted to, you could have your initial "open the database" code
> check for duplicates across those columns (and that 1 is the lowest
> Sequence for each Name) -- then at least you'd know that you'd had one of
> those bugs.
>
> J. Merrill
>



Pete
lcSQL Software <http://www.lcsql.com>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

Yes, thanks. I was mistaken. 

On Sep 6, 2013, at 9:27 PM, "James K. Lowden"  wrote:

> On Fri, 6 Sep 2013 07:56:53 -0500
> "Marc L. Allen"  wrote:
> 
>> I don't think it's a bug.  
> 
> It is a bug as long as the behavior is in exception to the
> documentation. 
> 
>> I don't believe there's any defined rule for how SQL should behave,
>> is there?  
> 
> Of course there is.  Hundreds of pages describe SQL.  
> 
>> The updates are done serially not atomically.  
> 
> They're not *supposed* to be.  Updates are absolutely atomic.  Every
> SQL statement is atomic.  
> 
> --jkl
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Luuk]

On 07-09-2013 03:27, James K. Lowden wrote:

On Fri, 6 Sep 2013 11:07:27 -0400
Richard Hipp  wrote:


The effect of early row updates might be visible in later row updates
if you contrive a *sufficiently* complex example.  But you really have
to go out of your way to do that.


sqlite> create table i ( i int primary key );
sqlite> insert into i values (1);
sqlite> insert into i values (2);
sqlite> update i set i = i + 1;
SQL error: column i is not unique


sqlite> drop table i;
sqlite> create table i ( i int primary key );
sqlite> insert into i values (2);
sqlite> insert into i values (1);
sqlite> update i set i = i + 1;
sqlite>

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

Re: [sqlite] UPDATE question

[ibrahim]

Further comment :

If you want to implement a sequence of records in a table you can do it 
much faster with only on record to update when you insert a value in the 
middle of this sequence. For this purpose you shouldn't use a pseudo 
array but a single or double ended queue with only one trigger.


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

Re: [sqlite] UPDATE question

[ibrahim]

workaround for your problem :

create table t1 (pk integer primary key, name text, seq integer) ;
create unique index idxt1 on t1 (name,seq) ;

insert into t1 values (1, 'blue', 1) ;
insert into t1 values (2, 'blue', 2) ;
insert into t1 values (3, 'blue', 3) ;
insert into t1 values (4, 'blue', 4) ;
insert into t1 values (5, 'blue', 5) ;

before updating your unique index just drop and recreate it !

Behaviour till now :

sqlite> select *from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5

sqlite> update t1 set seq=seq+1 where pk in (select pk from t1 where seq 
>= 3 order by seq desc) ;

Error: columns name, seq are not unique

Behaviour with the workaround :

drop index idxt1 ;
sqlite> update t1 set seq=seq+1 where seq >= 3 ;
sqlite> create unique index idxt1 on t1(name,seq) ;
sqlite> select * from t1 ;
sqlite> insert into t1 (name, seq) values ('blue',3) ;
sqlite> select * from t1 ;
1|blue|1
2|blue|2
6|blue|3
3|blue|4
4|blue|5
5|blue|6


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

Re: [sqlite] UPDATE question

[James K. Lowden]

On Fri, 6 Sep 2013 17:45:59 -0400 (EDT)
j.merr...@enlyton.com wrote:

> I propose that you remove the unique index because SQLite does not
> handle the update case the way you want. 

The correct general approach, in light of observed behavior, 

1.  begin IMMEDIATE transaction
2.  select rows into a temporary table
3.  update temporary table
4.  delete from main table
5.  insert into main table from temporary table
6.  commit

An alternative in this case would be to expand the unique constraint
with another column to support updates.  Something like this:

create table t 
( PKey INTEGER PRIMARY KEY
, Name TEXT
, Sequence INTEGER
, _pending INTEGER default 0 check (_pending in (PKey, 0))
, unique (Name, Sequence, _pending)
);

insert into t (Name, Sequence) values ('Blue', 1);
insert into t (Name, Sequence) values ('Blue', 2);
insert into t (Name, Sequence) values ('Blue', 3);
insert into t (Name, Sequence) values ('Blue', 4);
insert into t (Name, Sequence) values ('Blue', 5);

-- insert a new 3

BEGIN TRANSACTION ;
UPDATE t
set _pending = PKey where Sequence >=3;

UPDATE t
SET Sequence = Sequence + 1 
WHERE Sequence >= 3 
AND Name = 'Blue';

UPDATE t set _pending = 0;

insert into t (Name, Sequence) values ('Blue', 3);
COMMIT;

select * from t;
PKeyNameSequence_pending  
--  --  --  --
1   Blue1   0 
2   Blue2   0 
3   Blue4   0 
4   Blue5   0 
5   Blue6   0 
6   Blue3   0 

That has the effect of voiding the unique constraint while _pending is
nonzero, but leaving it in force after the commit.  

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

Re: [sqlite] UPDATE question

[James K. Lowden]

On Fri, 6 Sep 2013 11:07:27 -0400
Richard Hipp  wrote:

> The effect of early row updates might be visible in later row updates
> if you contrive a sufficiently complex example.  But you really have
> to go out of your way to do that.  

sqlite> create table i ( i int primary key );
sqlite> insert into i values (1);
sqlite> insert into i values (2);
sqlite> update i set i = i + 1;
SQL error: column i is not unique

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

Re: [sqlite] UPDATE question

[j . merrill]

I propose that you remove the unique index because SQLite does not handle the 
update case the way you want. (I'd call that a bug, but sometimes "liteness" 
means that Dr Hipp can say "I'm not going to fix it" and we have to respect his 
decision.)

Is there a reason other than "if you have a particular kind of bug in your 
code, you could end up violating the [Name/Sequence is unique] rule" to keep 
the index, when its presence causes you trouble? (Perhaps the answer is "users 
edit this table manually using other software" so you need the index to keep 
them from screwing up. But I doubt it, or they'd have complained what a pain it 
is to add a new row in the middle!)

There is little to prevent you from having other bugs that might be equally bad 
--

- putting both "Blue" and "blue" in the Name column (with separate sets of 
Sequence values) when both values shouldn't be there because the business 
context says they're the same
- have Sequence values not starting at 1 (e.g. 2 3 4) for a particular Name -- 
perhaps that wouldn't cause any trouble in other logic, but it probably would

If you wanted to, you could have your initial "open the database" code check 
for duplicates across those columns (and that 1 is the lowest Sequence for each 
Name) -- then at least you'd know that you'd had one of those bugs.

J. Merrill

-Original Message-
From: Peter Haworth
Sent: Thursday, September 05, 2013 2:21 PM
To: sqlite-users
Subject: [sqlite] UPDATE question

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY 
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE


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

Re: [sqlite] UPDATE question

[Simon Slavin]

On 6 Sep 2013, at 4:08pm, Simon Slavin  wrote:

> Right.  As I posted in my message that I had tested, this isn't being done 
> correctly.  A conflict isn't a conflict until the write, and the write 
> doesn't happen until the COMMIT. Therefore conflict testing needs to happen 
> at the commit, for /all/ the changes in the transaction, whether they're all 
> the consequence of one UPDATE or even of separate commands within the same 
> transaction.

On further thought, I may be wrong about that.  If it was really true then if 
you explicitly declared a transaction with BEGIN ... END a SQLITE_CONSTRAINT 
result due to violations of UNIQUE could only be returned at the END.  You 
wouldn't be able to tell which command within the transaction caused the 
conflict.

I don't know whether that's the way things really should work.  Or whether this 
is something the people who thought up SQL specified.

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

Re: [sqlite] UPDATE question

[Igor Tandetnik]

On 9/6/2013 1:05 PM, ibrahim wrote:

Perhaps you should really try it out cause my suggestion is totally
different from yours it uses a temporary table it works.

create table t1 (pk integer primary key, name text, seq integer) ;


You missed the whole "Name/Sequence pair of columns is defined as 
UNIQUE" part. Make it


create table t1 (pk integer primary key, name text, seq integer, 
UNIQUE(name, seq) ) ;


See how well your technique is working for you now.
--
Igor Tandetnik

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

Re: [sqlite] UPDATE question

[ibrahim]

On 06.09.2013 20:52, Igor Tandetnik wrote:

On 9/6/2013 1:05 PM, ibrahim wrote:

Perhaps you should really try it out cause my suggestion is totally
different from yours it uses a temporary table it works.

create table t1 (pk integer primary key, name text, seq integer) ;


You missed the whole "Name/Sequence pair of columns is defined as 
UNIQUE" part. Make it


create table t1 (pk integer primary key, name text, seq integer, 
UNIQUE(name, seq) ) ;


See how well your technique is working for you now.

I missed that part.

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

Re: [sqlite] UPDATE Question

[Peter Haworth]

Thanks Marc and all who responded.  Marc's suggestion seems to come the
closest to what I was looking for.

I did try one other thing which I thought might solve the problem.  Instead
of defining the Name/Sequence as UNIQUE, I set up a UNIQUE index for those
columns and defined both of them to be sorted DESC.  I then tried:

UPDATE Test INDEXED BY TestIndex SET Sequence=Sequence+1 WHERE Name='Blue'
AND Sequence>=3

I was surprised that I still got the error that Name/Sequence were not
unique since I thought the index would force the updates to be done
starting at the highest sequence number.  EXPLAIN QUERY PLAN indicates that
the TestIndex index was used.

Pete

On Fri, Sep 6, 2013 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:

> Message: 27
> Date: Fri, 6 Sep 2013 07:50:39 -0500
> From: "Marc L. Allen" <mlal...@outsitenetworks.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] UPDATE question
> Message-ID:
> <e7544051a0971a48b80ab118ac58918e05fb7da...@mbx03.exg5.exghost.com
> >
> Content-Type: text/plain; charset="us-ascii"
>
> No one commented on my second thread (written after I actually understood
> the problem!).
>
> But, I proposed a two update sequence to do it.
>
> UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >=
> seq_to_insert AND Name = name_to_insert
>
> UPDATE table SET Sequence = -Sequence WHERE Sequence < 0  AND Name =
> name_to_insert
>



Pete
lcSQL Software <http://www.lcsql.com>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[ibrahim]

On 06.09.2013 18:30, Dominique Devienne wrote:

On Fri, Sep 6, 2013 at 5:41 PM, ibrahim  wrote:



You can let sqlite handle the creation of a temporary table by :

update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3
order by seq desc) ;

afterwards you can insert.


Unless I'm misunderstanding you, I already tried that in an earlier post of
this thread, and that didn't work. Give it a try. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Perhaps you should really try it out cause my suggestion is totally 
different from yours it uses a temporary table it works.


create table t1 (pk integer primary key, name text, seq integer) ;
insert into t1 (pk, name, seq) values (1, "blue", 1) ;
insert into t1 (pk, name, seq) values (2, "blue", 2) ;
insert into t1 (pk, name, seq) values (3, "blue", 3) ;
insert into t1 (pk, name, seq) values (4, "blue", 4) ;
insert into t1 (pk, name, seq) values (5, "blue", 5) ;

sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5


sqlite> update t1 set seq=seq+1 where pk in (select pk from t1 where seq 
>= 3 order by seq desc) ;

sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|4
4|blue|5
5|blue|6


sqlite> insert into t1 (pk, name, seq) values (6, 'blue', 3) ;
sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|4
4|blue|5
5|blue|6
6|blue|3


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

Re: [sqlite] UPDATE question

[ibrahim]

On 06.09.2013 18:30, Dominique Devienne wrote:

On Fri, Sep 6, 2013 at 5:41 PM, ibrahim  wrote:



You can let sqlite handle the creation of a temporary table by :

update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3
order by seq desc) ;

afterwards you can insert.


Unless I'm misunderstanding you, I already tried that in an earlier post of
this thread, and that didn't work. Give it a try. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Perhaps you should really try it out cause my suggestion is totally 
different from yours it uses a temporary table it works.


create table t1 (pk integer primary key, name text, seq integer) ;
insert into t1 (pk, name, seq) values (1, "blue", 1) ;
insert into t1 (pk, name, seq) values (2, "blue", 2) ;
insert into t1 (pk, name, seq) values (3, "blue", 3) ;
insert into t1 (pk, name, seq) values (4, "blue", 4) ;
insert into t1 (pk, name, seq) values (5, "blue", 5) ;

sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5


sqlite> update t1 set seq=seq+1 where pk in (select pk from t1 where seq 
>= 3 order by seq desc) ;

sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|4
4|blue|5
5|blue|6


sqlite> insert into t1 (pk, name, seq) values (6, 'blue', 3) ;
sqlite> select * from t1 ;
1|blue|1
2|blue|2
3|blue|4
4|blue|5
5|blue|6
6|blue|3



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

Re: [sqlite] UPDATE question

[ibrahim]

On 05.09.2013 20:20, Peter Haworth wrote:

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE

I need to insert a new row into a point between two sequence numbers.  For
example, if the existing rows are:

Name   Sequence
---
Blue   1
Blue   2
Blue3
Blue   4
Blue   5

... I might need to insert a new Blue/3.

Currently I'm doing this by selecting the Pkey values of the entries with
sequence >=3, sorting them by descending sequence, then a loop with an
UPDATE to set the sequence to sequence+1 for each PKey value so 5 becomes
6, 4 becomes 5, 3 becomes 4.  Then I INSERT a new Blue/3 row.

That works fine but wondering if there might be a single UPDATE statement
that could do this for me.  I can use the WHERE clause to select sequence
3,4, and 5 but  the UPDATE has to process the rows in descending sequence
order to avoid UNIQUE conflicts.


Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


You can let sqlite handle the creation of a temporary table by :

update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3 
order by seq desc) ;


afterwards you can insert.

If you also want to change the pk order just reset that to in the update.

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

Re: [sqlite] UPDATE question

[Dominique Devienne]

On Fri, Sep 6, 2013 at 5:41 PM, ibrahim  wrote:


> You can let sqlite handle the creation of a temporary table by :
>
> update t1 set seq=seq+1 where pk in (select pk from t1 where seq >= 3
> order by seq desc) ;
>
> afterwards you can insert.


Unless I'm misunderstanding you, I already tried that in an earlier post of
this thread, and that didn't work. Give it a try. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

MySQL also uses this implementation.  They acknowledge that it is not SQL 
compliant and that (I never thought of this), you cannot delete a record that 
has a foreign key link to itself.

Postgres apparently has the ability to have deferred checking as of V9, but not 
before then.

Please see:

http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Friday, September 06, 2013 11:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne <ddevie...@gmail.com>wrote:

> Myself, if I'm "thinking in sets", all implementation details aside, 
> the UPDATE statement looks fine and correct, and I'd have expected 
> SQLite to support it.
>
> But I'm just waiting to read Dr. Hipp's own read on this now. --DD



I'm busy with a different problem and don't have time to study your thread, so 
I'm guessing at the answer:

The UPDATE statement in SQLite operates row-by-row.  The effect of early row 
updates might be visible in later row updates if you contrive a sufficiently 
complex example.  But you really have to go out of your way to do that.  If a 
constraint error happens, the entire UPDATE statement is rolled back (except if 
OR FAIL is specified - see the docs).

Yes, I know this is not "relational".  No, I do not intend to fix it.


> -
>
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Dan Kennedy]

On 09/06/2013 10:19 PM, Marc L. Allen wrote:

MySQL also uses this implementation.  They acknowledge that it is not SQL 
compliant and that (I never thought of this), you cannot delete a record that 
has a foreign key link to itself.

Postgres apparently has the ability to have deferred checking as of V9, but not 
before then.


SQLite defers checking FK constraints until the end of
the statement (or the end of the transaction, for
DEFERRABLE INITIALLY DEFERRED constraints). They are the
exception though - all others (CHECK, UNIQUE etc.) are
checked as part of updating/deleting/inserting each row.

Dan.





Please see:

http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Friday, September 06, 2013 11:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne <ddevie...@gmail.com>wrote:


Myself, if I'm "thinking in sets", all implementation details aside,
the UPDATE statement looks fine and correct, and I'd have expected
SQLite to support it.

But I'm just waiting to read Dr. Hipp's own read on this now. --DD



I'm busy with a different problem and don't have time to study your thread, so 
I'm guessing at the answer:

The UPDATE statement in SQLite operates row-by-row.  The effect of early row 
updates might be visible in later row updates if you contrive a sufficiently 
complex example.  But you really have to go out of your way to do that.  If a 
constraint error happens, the entire UPDATE statement is rolled back (except if 
OR FAIL is specified - see the docs).

Yes, I know this is not "relational".  No, I do not intend to fix it.



-


D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] UPDATE question

[Simon Slavin]

On 6 Sep 2013, at 1:50pm, Marc L. Allen  wrote:

> No one commented on my second thread (written after I actually understood the 
> problem!).
> 
> But, I proposed a two update sequence to do it.  
> 
> UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert 
> AND Name = name_to_insert
> 
> UPDATE table SET Sequence = -Sequence WHERE Sequence < 0  AND Name = 
> name_to_insert
> 
> I've used this system many times to avoid conflicts, but it may not work 
> where the table needs to be accessed concurrently, as rows will sort of 
> disappear temporarily (or at least change to an unusable state).

Yep, this is a find, acceptable, and reasonably fast work-around.  Nice one.



On 6 Sep 2013, at 5:21am, James K. Lowden  wrote:

> Simon Slavin  wrote:
> 
>> If all the updates are done inside a COMMIT,
>> then the conflict may not be recognised because by the time the first
>> change is written back to the table, the conflicting entry has
>> already been renumbered.
> 
> I was puzzled by this thread, so ran my own little test.  To my dismay,
> UPDATE is not atomic in SQLite.  

Right.  As I posted in my message that I had tested, this isn't being done 
correctly.  A conflict isn't a conflict until the write, and the write doesn't 
happen until the COMMIT.  Therefore conflict testing needs to happen at the 
commit, for /all/ the changes in the transaction, whether they're all the 
consequence of one UPDATE or even of separate commands within the same 
transaction.

Possibly another problem that could be fixed in SQLite4.

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

Re: [sqlite] UPDATE question

[Marc L. Allen]

Nice, but that still requires extra work.

1) Determine if row is already in table.
2) Determine next lower value.
3) Split difference and insert.

There's also the possibility that the higher level APP expects the new row to 
have a sequence number of 3.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of kyan
Sent: Friday, September 06, 2013 10:41 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question

On Thu, Sep 5, 2013 at 9:20 PM, Peter Haworth <p...@lcsql.com> wrote:

> I have a table with the following (simplified) structure
>
> PKeyINTEGER PRIMARY KEY
> NameTEXT
> Sequence INTEGER
>
> The Name/Sequence pair of columns is defined as UNIQUE
>
> I need to insert a new row into a point between two sequence numbers.  
> For example, if the existing rows are:
>
> Name   Sequence
> ---
> Blue   1
> Blue   2
> Blue3
> Blue   4
> Blue   5
>
> ... I might need to insert a new Blue/3.
>
> If the Sequence column needs not be consecutive integers but just
specifies order, consider turning it to a float. Then you can insert a row 
between two existing rows with Sequence a and b by using their median (a +
b) / 2 as the new row's Sequence:

Blue   1
Blue   2
--> Blue(2 + 3) / 2 = 2.5
Blue3
Blue   4
Blue   5

and then:

Blue   1
Blue   2
--> Blue2.25
Blue2.5
 Blue3
Blue   4
Blue   5

and so on. This way you avoid having to modify following rows on each insertion.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Dominique Devienne]

On Fri, Sep 6, 2013 at 2:50 PM, Marc L. Allen
wrote:

> No one commented on my second thread (written after I actually understood
> the problem!).
>
> But, I proposed a two update sequence to do it.
>
> UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >=
> seq_to_insert AND Name = name_to_insert
> UPDATE table SET Sequence = -Sequence WHERE Sequence < 0  AND Name =
> name_to_insert
>

Because, no offense, that's just a work-around.

The question that matters is whether SQL requires the UPDATE statement to
succeed or not, despite a given implementation possibly having to deal with
transient index violations while processing the rows.

If so, SQLite would be non-compliant in that regard, as of now.
If not, and implementations are free to support or not such UPDATE
statements, then SQLite would ideally document this limitation, since it
departs from major DBMS's like Oracle.

Myself, if I'm "thinking in sets", all implementation details aside, the
UPDATE statement looks fine and correct, and I'd have expected SQLite to
support it.

But I'm just waiting to read Dr. Hipp's own read on this now. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Richard Hipp]

On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne wrote:

> Myself, if I'm "thinking in sets", all implementation details aside, the
> UPDATE statement looks fine and correct, and I'd have expected SQLite to
> support it.
>
> But I'm just waiting to read Dr. Hipp's own read on this now. --DD



I'm busy with a different problem and don't have time to study your thread,
so I'm guessing at the answer:

The UPDATE statement in SQLite operates row-by-row.  The effect of early
row updates might be visible in later row updates if you contrive a
sufficiently complex example.  But you really have to go out of your way to
do that.  If a constraint error happens, the entire UPDATE statement is
rolled back (except if OR FAIL is specified - see the docs).

Yes, I know this is not "relational".  No, I do not intend to fix it.


> -
>
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[kyan]

On Thu, Sep 5, 2013 at 9:20 PM, Peter Haworth  wrote:

> I have a table with the following (simplified) structure
>
> PKeyINTEGER PRIMARY KEY
> NameTEXT
> Sequence INTEGER
>
> The Name/Sequence pair of columns is defined as UNIQUE
>
> I need to insert a new row into a point between two sequence numbers.  For
> example, if the existing rows are:
>
> Name   Sequence
> ---
> Blue   1
> Blue   2
> Blue3
> Blue   4
> Blue   5
>
> ... I might need to insert a new Blue/3.
>
> If the Sequence column needs not be consecutive integers but just
specifies order, consider turning it to a float. Then you can insert a row
between two existing rows with Sequence a and b by using their median (a +
b) / 2 as the new row's Sequence:

Blue   1
Blue   2
--> Blue(2 + 3) / 2 = 2.5
Blue3
Blue   4
Blue   5

and then:

Blue   1
Blue   2
--> Blue2.25
Blue2.5
 Blue3
Blue   4
Blue   5

and so on. This way you avoid having to modify following rows on each
insertion.

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

Re: [sqlite] UPDATE question

[Igor Tandetnik]

On 9/6/2013 8:50 AM, Marc L. Allen wrote:

But, I proposed a two update sequence to do it.

UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND 
Name = name_to_insert
UPDATE table SET Sequence = -Sequence WHERE Sequence < 0  AND Name = 
name_to_insert

I've used this system many times to avoid conflicts, but it may not work where 
the table needs to be accessed concurrently, as rows will sort of disappear 
temporarily (or at least change to an unusable state).


Well, that's exactly what transactions are there for.
--
Igor Tandetnik

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

Re: [sqlite] UPDATE question

[Marc L. Allen]

I don't think it's a bug.  I don't believe there's any defined rule for how SQL 
should behave, is there?  The updates are done serially not atomically.  If the 
rows happen to be processed in reverse order, then no constraint is violated.  
In fact, if there was a way to define the order the update was performed, it 
might be useful as a tool to solve this kind of problem.  

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Friday, September 06, 2013 3:28 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question


By forcing the "physical order" of the rows to be reversed, the UPDATE succeeds.
I suspect this is just a bug, and Dr Hipp will fix it (and if not document it 
somehow).




This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

No one commented on my second thread (written after I actually understood the 
problem!).

But, I proposed a two update sequence to do it.  

UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND 
Name = name_to_insert

UPDATE table SET Sequence = -Sequence WHERE Sequence < 0  AND Name = 
name_to_insert

I've used this system many times to avoid conflicts, but it may not work where 
the table needs to be accessed concurrently, as rows will sort of disappear 
temporarily (or at least change to an unusable state).

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Friday, September 06, 2013 3:28 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden <jklow...@schemamania.org>wrote:

> Here's how Marc L. Allen's query should work (different DBMS):
>
> $ bsqldb  < /tmp/sql
>PKey  Name   Sequence
> ---  --  ---
>   1  Blue  1
>   2  Blue  2
>   3  Blue  4
>   4  Blue  5
>   5  Blue  6
>   6  Blue  3
> 6 rows affected
>
> Note that the final insert is assigned the next auto-generated PKey 
> (6), and the old 3 is now 4, etc.
>

FWIW, a repro, taking the unrelated PKey out of the picture:

C:\Users\DDevienne>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions 
Enter SQL statements terminated with a ";"
sqlite> create table jkl (name text, seq number, unique (name, seq)); 
sqlite> insert into jkl values ('blue', 1), ('blue', 2), ('blue', 3),
('blue', 4), ('blue', 5);
sqlite> select * from jkl;
blue|1
blue|2
blue|3
blue|4
blue|5
sqlite> update jkl set seq = seq + 1 where seq >= 3 and name = 'blue';
Error: columns name, seq are not unique
sqlite>

I tried to "ruse" and use

sqlite> update jkl set seq = seq + 1 where name = 'blue' and seq in 
sqlite> (select
seq from jkl where seq >= 3 order by seq desc);
Error: columns name, seq are not unique

but of course you cannot influence the processing order SQLite uses. OK, you 
can in a way, see below:

sqlite> create table jk2 as select * from jkl order by seq desc; select 
sqlite> * from jk2;
blue|5
blue|4
blue|3
blue|2
blue|1
sqlite> update jk2 set seq = seq + 1 where seq >= 3 and name = 'blue'; 
sqlite> select * from jk2;
blue|6
blue|5
blue|4
blue|2
blue|1
sqlite> insert into jk2 values ('blue', 3); select * from jk2;
blue|6
blue|5
blue|4
blue|2
blue|1
blue|3
sqlite>

By forcing the "physical order" of the rows to be reversed, the UPDATE succeeds.
I suspect this is just a bug, and Dr Hipp will fix it (and if not document it 
somehow).

For reference, the same in Oracle:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table jkl
  2  ( name varchar2(64)
  3  , seq  number
  4  , unique (name, seq)
  5  );

Table created.

SQL> insert into jkl values ('blue', 1);

1 row created.

SQL> insert into jkl values ('blue', 2);

1 row created.

SQL> insert into jkl values ('blue', 3);

1 row created.

SQL> insert into jkl values ('blue', 4);

1 row created.

SQL> insert into jkl values ('blue', 5);

1 row created.

SQL> commit;

Commit complete.

SQL> column name format a16;
SQL> select * from jkl;

NAMESEQ
 --
blue  1
blue  2
blue  3
blue  4
blue  5

SQL> update jkl set seq = seq + 1 where seq >= 3 and name = 'blue';

3 rows updated.

SQL> select * from jkl;

NAMESEQ
 --
blue  1
blue  2
blue  4
blue  5
blue  6

SQL> insert into jkl values ('blue', 3);

1 row created.

SQL> select * from jkl;

NAMESEQ
 --
blue  1
blue  2
blue  4
blue  5
blue  6
blue  3

6 rows selected.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confident

Re: [sqlite] UPDATE question

[Dominique Devienne]

On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden wrote:

> Here's how Marc L. Allen's query should work (different DBMS):
>
> $ bsqldb  < /tmp/sql
>PKey  Name   Sequence
> ---  --  ---
>   1  Blue  1
>   2  Blue  2
>   3  Blue  4
>   4  Blue  5
>   5  Blue  6
>   6  Blue  3
> 6 rows affected
>
> Note that the final insert is assigned the next auto-generated
> PKey (6), and the old 3 is now 4, etc.
>

FWIW, a repro, taking the unrelated PKey out of the picture:

C:\Users\DDevienne>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table jkl (name text, seq number, unique (name, seq));
sqlite> insert into jkl values ('blue', 1), ('blue', 2), ('blue', 3),
('blue', 4), ('blue', 5);
sqlite> select * from jkl;
blue|1
blue|2
blue|3
blue|4
blue|5
sqlite> update jkl set seq = seq + 1 where seq >= 3 and name = 'blue';
Error: columns name, seq are not unique
sqlite>

I tried to "ruse" and use

sqlite> update jkl set seq = seq + 1 where name = 'blue' and seq in (select
seq from jkl where seq >= 3 order by seq desc);
Error: columns name, seq are not unique

but of course you cannot influence the processing order SQLite uses. OK,
you can in a way, see below:

sqlite> create table jk2 as select * from jkl order by seq desc;
sqlite> select * from jk2;
blue|5
blue|4
blue|3
blue|2
blue|1
sqlite> update jk2 set seq = seq + 1 where seq >= 3 and name = 'blue';
sqlite> select * from jk2;
blue|6
blue|5
blue|4
blue|2
blue|1
sqlite> insert into jk2 values ('blue', 3);
sqlite> select * from jk2;
blue|6
blue|5
blue|4
blue|2
blue|1
blue|3
sqlite>

By forcing the "physical order" of the rows to be reversed, the UPDATE
succeeds.
I suspect this is just a bug, and Dr Hipp will fix it (and if not document
it somehow).

For reference, the same in Oracle:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL> create table jkl
  2  ( name varchar2(64)
  3  , seq  number
  4  , unique (name, seq)
  5  );

Table created.

SQL> insert into jkl values ('blue', 1);

1 row created.

SQL> insert into jkl values ('blue', 2);

1 row created.

SQL> insert into jkl values ('blue', 3);

1 row created.

SQL> insert into jkl values ('blue', 4);

1 row created.

SQL> insert into jkl values ('blue', 5);

1 row created.

SQL> commit;

Commit complete.

SQL> column name format a16;
SQL> select * from jkl;

NAMESEQ
 --
blue  1
blue  2
blue  3
blue  4
blue  5

SQL> update jkl set seq = seq + 1 where seq >= 3 and name = 'blue';

3 rows updated.

SQL> select * from jkl;

NAMESEQ
 --
blue  1
blue  2
blue  4
blue  5
blue  6

SQL> insert into jkl values ('blue', 3);

1 row created.

SQL> select * from jkl;

NAMESEQ
 --
blue  1
blue  2
blue  4
blue  5
blue  6
blue  3

6 rows selected.

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

Re: [sqlite] UPDATE question

[Dominique Devienne]

On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden wrote:

> That's perfectly good SQL.  SQLite is simply not executing the
> update atomically.
>
> Anyone tempted to protest may be forgetting "atomic" means more than
> "all or nothing". It also means the DBMS may execute the transaction
> however it sees fit, at the cost of ensuring that constraints remain in
> effect upon commit but *only* upon commit. ...


> Constraints hold for the whole database at all times whenever the user
> can see the data.  Any update is valid if it can logically be applied
> and leave the database in a state consistent with its declared
> constraints.
>

FWIW, in the circumstances of the thread below, SQLite does behave as you
described it should in this new UPDATE use-case, i.e. SQLite copes with
temporarily invalid states provided the final state is valid. So it's not
necessarily a philosophical design decision, but maybe an implementation
issue, which is perhaps fixable. My $0.02, before a more authoritative
answer.

http://sqlite.1065341.n5.nabble.com/Order-of-ON-DELETE-CASCADE-specified-in-SQLite-td67681.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[James K. Lowden]

On Thu, 5 Sep 2013 19:53:15 +0100
Simon Slavin  wrote:

> On 5 Sep 2013, at 7:20pm, Peter Haworth  wrote:
> 
> > That works fine but wondering if there might be a single UPDATE
> > statement that could do this for me.  I can use the WHERE clause to
> > select sequence 3,4, and 5 but  the UPDATE has to process the rows
> > in descending sequence order to avoid UNIQUE conflicts.
> 
> An interesting point.  If all the updates are done inside a COMMIT,
> then the conflict may not be recognised because by the time the first
> change is written back to the table, the conflicting entry has
> already been renumbered.

I was puzzled by this thread, so ran my own little test.  To my dismay,
UPDATE is not atomic in SQLite.  

$ rm -f db; sqlite3 -echo db < sql
create table t 
( PKey INTEGER PRIMARY KEY
, Name TEXT
, Sequence INTEGER
, unique (Name, Sequence)
);

insert into t (Name, Sequence) values ('Blue', 1);
insert into t (Name, Sequence) values ('Blue', 2);
insert into t (Name, Sequence) values ('Blue', 3);
insert into t (Name, Sequence) values ('Blue', 4);
insert into t (Name, Sequence) values ('Blue', 5);

-- insert a new 3

BEGIN TRANSACTION ;
UPDATE t 
SET Sequence = Sequence + 1 
WHERE Sequence >= 3 
AND Name = 'Blue';
SQL error near line 17: columns Name, Sequence are not unique

That's perfectly good SQL.  SQLite is simply not executing the
update atomically.  

Anyone tempted to protest may be forgetting "atomic" means more than
"all or nothing". It also means the DBMS may execute the transaction
however it sees fit, at the cost of ensuring that constraints remain in
effect upon commit but *only* upon commit. That's why this works:

sqlite> create table a (a int, b int check( a + b = 3));
sqlite> insert into a values (1, 2);
sqlite> select * from a;
a   b 
--  --
1   2 
sqlite> update a set a = b, b = a;
sqlite> select * from a;
a   b 
--  --
2   1 

Constraints hold for the whole database at all times whenever the user
can see the data.  Any update is valid if it can logically be applied
and leave the database in a state consistent with its declared
constraints.  

Here's how Marc L. Allen's query should work (different
DBMS):

$ bsqldb  < /tmp/sql 
   PKey  Name   Sequence
---  --  ---
  1  Blue  1
  2  Blue  2
  3  Blue  4
  4  Blue  5
  5  Blue  6
  6  Blue  3
6 rows affected

Note that the final insert is assigned the next auto-generated 
PKey (6), and the old 3 is now 4, etc.  

Granted, it's not easy.  The update processing cannot simply find each
row in turn and update it (as appears to be the case now). One
alternative would be to make a copy of the whole set, update it, and
then apply it en masse.  Another probably slower way would be to "keep
trying": if a row can't be updated, find one that can and continue
until they're all done or you're painted into a corner and
have to rollback.  Doubtless there are better ways.  

No one ever said a DBMS was a simple creature!  

Simon mentioned 

> http://www.sqlite.org/conflict.html

This has nothing to do with deferred constraint resolution.  Deferred
constraint resolution involves more than one table being updated in a
transaction, deferring constraint enforcement until the
commit.  See for example this bit of DB2 documentation, 
http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_0633.htm.
  

I also looked at http://www.sqlite.org/lang_conflict.html, which says, 

"REPLACE
When a UNIQUE constraint violation occurs, the REPLACE algorithm
deletes pre-existing rows that are causing the constraint violation
prior to inserting or updating the current row and the command
continues executing normally."

The order in which the rows are (internally) processed is arbitrary,
and the results of using "or replace" here are predictably
unpredictable:

BEGIN TRANSACTION ;
UPDATE or replace t 
SET Sequence = Sequence + 1 
WHERE Sequence >= 3 
AND Name = 'Blue';

insert into t (Name, Sequence) values ('Blue', 3);
COMMIT;

select * from t;
PKeyNameSequence  
--  --  --
1   Blue1 
2   Blue2 
3   Blue4 
5   Blue6 
6   Blue3 

but what's a Blue 5 among friends?  :-/  

As regards the documentation, the only fair thing to do for now would
be to add UPDATE to the omitted page, explaining the row-by-rowism.  
I would also suggest flagging the UPDATE page itself, because the
behavior is very much at 

Re: [sqlite] UPDATE question

[Simon Slavin]

On 5 Sep 2013, at 7:20pm, Peter Haworth  wrote:

> That works fine but wondering if there might be a single UPDATE statement
> that could do this for me.  I can use the WHERE clause to select sequence
> 3,4, and 5 but  the UPDATE has to process the rows in descending sequence
> order to avoid UNIQUE conflicts.

An interesting point.  If all the updates are done inside a COMMIT, then the 
conflict may not be recognised because by the time the first change is written 
back to the table, the conflicting entry has already been renumbered.

Nope, apparently SQLite doesn't support deferring conflict resolution until the 
COMMIT [1].  Maybe that should be added to these two pages:




Simon.

[1] Except for FOREIGN KEYS.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

Oops.. sorry.. I missed the last paragraph.  

If you're essentially single threaded.. I can do it in two updates...

UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND 
Name = name_to_insert
UPDATE table SET Sequence = -Sequence WHERE Sequence < 0  AND Name = 
name_to_insert

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Peter Haworth
Sent: Thursday, September 05, 2013 2:21 PM
To: sqlite-users
Subject: [sqlite] UPDATE question

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE

I need to insert a new row into a point between two sequence numbers.  For 
example, if the existing rows are:

Name   Sequence
---
Blue   1
Blue   2
Blue3
Blue   4
Blue   5

... I might need to insert a new Blue/3.

Currently I'm doing this by selecting the Pkey values of the entries with 
sequence >=3, sorting them by descending sequence, then a loop with an UPDATE 
to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 
5, 3 becomes 4.  Then I INSERT a new Blue/3 row.

That works fine but wondering if there might be a single UPDATE statement that 
could do this for me.  I can use the WHERE clause to select sequence 3,4, and 5 
but  the UPDATE has to process the rows in descending sequence order to avoid 
UNIQUE conflicts.


Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

How about... ?

UPDATE table SET Sequence = Sequence + 1 WHERE Sequence >= seq_to_insert AND 
Name = name_to_insert

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Peter Haworth
Sent: Thursday, September 05, 2013 2:21 PM
To: sqlite-users
Subject: [sqlite] UPDATE question

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE

I need to insert a new row into a point between two sequence numbers.  For 
example, if the existing rows are:

Name   Sequence
---
Blue   1
Blue   2
Blue3
Blue   4
Blue   5

... I might need to insert a new Blue/3.

Currently I'm doing this by selecting the Pkey values of the entries with 
sequence >=3, sorting them by descending sequence, then a loop with an UPDATE 
to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 
5, 3 becomes 4.  Then I INSERT a new Blue/3 row.

That works fine but wondering if there might be a single UPDATE statement that 
could do this for me.  I can use the WHERE clause to select sequence 3,4, and 5 
but  the UPDATE has to process the rows in descending sequence order to avoid 
UNIQUE conflicts.


Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Donald Griggs]

Hi, Barry,

Regarding:  "Could you please explain me why the indexing is so important?
(for
future reference)."

If you're asking just in *general* why indexes can speed up searching a
database table, you might want to look at most any sql tutorial, or
resources such as:
http://en.wikipedia.org/wiki/Index_%28database%29

You might then want to look at the sqlite document for the
EXPLAIN QUERY PLAN

If I've misunderstood your question and you're asking something much more
specific, I apologize in advance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Barry1337]

Igor Tandetnik wrote:
> 
> Barry1337  wrote:
>> So I need, for every record in STAYSPEC, to find another record in
>> STAYSPEC
>> that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I
>> want to replace the date_out from STAYSPEC with that date (in text
>> format).
>> 
>> If such a record does not exist (EXISTS) then it needs to take the
>> date_out
>> from the STAYHOSP table where the STAYNUM is the same.
> 
> Try this:
> 
> update STAYSPEC set date_out = coalesce(
>   (select date_in from STAYSPEC ss2
>where ss2.staynum = STAYSPEC.staynum and
>ss2.order_spec = STAYSPEC.order_spec + 1),
>   (select date_out from STAYHOSP sh where sh.staynum = STAYSPEC.staynum)
> );
> 
>> Whenever I execute the above query it doesn't give an error or something,
>> it's just keeps running without ever stopping !
> 
> Make sure you have an index on STAYSPEC(staynum, order_spec) (or at least
> on STAYSPEC(staynum) ), as well as one on STAYHOSP(staynum)
> 
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

Thanks! You made my day :) Apparantly the indexing stopped the infinite
querying. Could you please explain me why the indexing is so important? (for
future reference). 

-- 
View this message in context: 
http://old.nabble.com/UPDATE-question-tp32673794p32677141.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] UPDATE question

[Marshall Cline]

Because your query was fairly complex, to help everyone debug it, I
simplified it using simple names (tables are a, b, c; fields are m, n, x, y,
z) and much simpler use of whitespace / indentation:

UPDATE a
SET x = CASE
WHEN EXISTS 
(
SELECT * 
FROM a
AS b
WHERE b.n = a.n
AND b.m = a.m + 1
)
THEN 
(
SELECT y
FROM a
AS c
WHERE c.n = a.n
AND c.m = a.m + 1
)
ELSE 
(
SELECT d.z
FROM d 
WHERE d.n = a.n
)
END;

Note that the name of field 'z' (from table 'd') happens to be the same as
the name of field 'x' (from table 'a'), but since they were from different
tables, I chose different names above - obviously you'll need to translate
everything back to your names before any advice can be useful.

So that's it - I did this to help others debug your problem. However I will
offer an observation which may (or may not) help: You might want to try
using a qualified name in the SELECT within the THEN clause ("SELECT y"
rather than "SELECT c.y"). I'm not sure if a qualified name would help in
the SET clause, but you could also try that.

Marshall


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Barry1337
Sent: Tuesday, October 18, 2011 6:04 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] UPDATE question


I have the following query:

Code:

UPDATE STAYSPEC SET date_out =
CASE WHEN EXISTS 
 (SELECT * 
  FROM STAYSPEC AS STAYSPEC2 
  WHERE (STAYSPEC2.STAYNUM = STAYSPEC.STAYNUM) AND 
 (STAYSPEC2.ORDER_SPEC = STAYSPEC.ORDER_SPEC+1)) THEN 
 (SELECT date_in FROM STAYSPEC AS STAYSPEC3 
  WHERE (STAYSPEC3.STAYNUM = STAYSPEC.STAYNUM) 
  AND (STAYSPEC3.ORDER_SPEC = STAYSPEC.ORDER_SPEC+1)) ELSE 
  (SELECT STAYHOSP.date_out FROM STAYHOSP 
  WHERE (STAYHOSP.STAYNUM = STAYSPEC.STAYNUM)) END;

date_in and date_out have both the type TEXT.

So I need, for every record in STAYSPEC, to find another record in STAYSPEC
that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I
want to replace the date_out from STAYSPEC with that date (in text format).

If such a record does not exist (EXISTS) then it needs to take the date_out
from the STAYHOSP table where the STAYNUM is the same.

Whenever I execute the above query it doesn't give an error or something,
it's just keeps running without ever stopping ! Since it's quite hard to
debug this I sincerely hope someone can help me with this.
--
View this message in context:
http://old.nabble.com/UPDATE-question-tp32673794p32673794.html
Sent from the SQLite mailing list archive at Nabble.com.

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

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

Re: [sqlite] UPDATE question

[Igor Tandetnik]

Barry1337  wrote:
> So I need, for every record in STAYSPEC, to find another record in STAYSPEC
> that has the same STAYNUM field and an ORDER_SPEC field that is 1 more. I
> want to replace the date_out from STAYSPEC with that date (in text format).
> 
> If such a record does not exist (EXISTS) then it needs to take the date_out
> from the STAYHOSP table where the STAYNUM is the same.

Try this:

update STAYSPEC set date_out = coalesce(
  (select date_in from STAYSPEC ss2
   where ss2.staynum = STAYSPEC.staynum and
   ss2.order_spec = STAYSPEC.order_spec + 1),
  (select date_out from STAYHOSP sh where sh.staynum = STAYSPEC.staynum)
);

> Whenever I execute the above query it doesn't give an error or something,
> it's just keeps running without ever stopping !

Make sure you have an index on STAYSPEC(staynum, order_spec) (or at least on 
STAYSPEC(staynum) ), as well as one on STAYHOSP(staynum)

-- 
Igor Tandetnik

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

Re: [sqlite] Update question

[Igor Tandetnik]

Jon Dixon <[EMAIL PROTECTED]> wrote:
> I am updating how I handle dates/durations in a database of mine and
> I would like to come up with an SQL command to make the change for
> me.
>
> The old format had a field Departure (-MM-DD) and a field Nights
> (an integer), where the new format
> still uses Departure but adds Return (-MM-DD) and the duration is
> calculated from the difference between Departure and Return.
>
> My stab at an SQL statement to pull this change off is as follows:
> UPDATE
> Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT
> pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND
> Nights != '')
>
> (trying to get the field Return to be "Nights"
> days after Departure, so long as Return wasn't set already and Nights
> is set, in case my aim is not clear from the attempted SQL)

Perhaps something like this:

update Events set Return = date(Departure, Nights || ' days')
where Return is null and Nights is not null;

Igor Tandetnik 



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

Re: [sqlite] Update question

[Brad Stiles]

> My stab at an SQL statement to pull this change off is as follows:
> UPDATE
> Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT
> pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND Nights 
> != '')

Why do you think you need the sub-select?  Assuming that they syntax
for the date function is correct, why would the below not work?

UPDATE Events
SET Return = date(departure, nights + ' days')
WHERE Return = '' AND Nights != ''
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update question

[Cesar Rodas]

Thank you samuel

On 07/03/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:



Try this:

UPDATE items
SET price = (
SELECT price
FROM month
WHERE id = items.id)
WHERE id IN (SELECT id from month);

HTH,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]

-Original Message-
From: Cesar Rodas [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 07, 2007 2:53 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Update question

Hello to all  I want to know if sqlite supports updates to more than a
table, as mysql allows

Ex:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

Thanks to all



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
Cesar Rodas
http://www.sf.net/projects/pagerank (The PageRank made easy...)
http://www.sf.net/projects/fastfs ( The Fast File System)
Mobile Phone: 595 961 974165
Phone: 595 21 645590
[EMAIL PROTECTED]
[EMAIL PROTECTED]

RE: [sqlite] Update question

[Samuel R. Neff]

Try this:

UPDATE items
SET price = (
SELECT price
FROM month
WHERE id = items.id)
WHERE id IN (SELECT id from month);
 
HTH,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Cesar Rodas [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 07, 2007 2:53 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Update question

Hello to all  I want to know if sqlite supports updates to more than a
table, as mysql allows

Ex:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

Thanks to all


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