Two options (one based on Igor's answer)
update playYouTubVideo
set speed = case when key = 1 then (select speed from playYouTubeVIdeo when key
= 2)
when key = 2 then (select ... when key = 3)
...
when key = 5 then (select ... when key = 1)
I can't recall if the update will actually do that atomically (so that the
speed for key = 1 is still available).
Option 2:
Since I believe SQLite doesn't support UPDATE FROM, you'll need temporary
variables somewhere.
If the table is small enough, copy it off and do the updates using the copy as
a source. If the table is large, then maybe add in an extra staging field in
the record?
CREATE TABLE playYouTubeVideo (
key TEXT NOT NULL,
speed FLOAT NOT NULL,
tmpSpeed FLOAT NULL,
CONSTRAINT key CHECK(length(key) == 1),
CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
PRIMARY KEY(key)
);
Then before doing the cycle,
update playYouTubeVideo set tmpSpeed = speed
Then perform the update as David suggested, but using the tmpSpeed variable.
You can probably optimize that by using just tmpSpeed variable from the start
or end of the cycle.
Marc
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 4:37 PM
To: SQLite mailing list <[email protected]>
Subject: Re: [sqlite] Can this be done with SQLite
2018-01-22 21:38 GMT+01:00 David Raymond <[email protected]>:
> Unless I'm reading you wrong then just do the normal
>
> begin transaction;
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '2')
> where key = '1';
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '3')
> where key = '2';
> ...
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '5')
> where key = '1';
> commit;
>
Nope. By the way I see that I did not write it correctly. :'-(
When I do this, I get:
sqlite> SELECT *
...> FROM playYouTubeVideo
...> WHERE key BETWEEN '1' AND '5'
...> ;
1|1.0
2|2.0
3|3.0
4|4.0
5|5.0
sqlite> begin transaction;
sqlite> update playYouTubeVideo set speed = (
...> select speed from playYouTubeVideo where key = '2')
...> where key = '1';
sqlite> update playYouTubeVideo set speed = (
...> select speed from playYouTubeVideo where key = '3')
...> where key = '2';
sqlite> update playYouTubeVideo set speed = (
...> select speed from playYouTubeVideo where key = '4')
...> where key = '3';
sqlite> update playYouTubeVideo set speed = (
...> select speed from playYouTubeVideo where key = '5')
...> where key = '4';
sqlite> update playYouTubeVideo set speed = (
...> select speed from playYouTubeVideo where key = '1')
...> where key = '5';
sqlite> commit;
sqlite> SELECT *
...> FROM playYouTubeVideo
...> WHERE key BETWEEN '1' AND '5'
...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0
But I want the last one needs to be 1.0.
Also, when the range becomes big, it will be a lot of code.
I was hoping I overlooked a smart trick, but I probably need to do it
programmatically.
-----Original Message-----
> From: sqlite-users
> [mailto:[email protected]]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 3:30 PM
> To: SQLite mailing list
> Subject: [sqlite] Can this be done with SQLite
>
> I have the following table:
> CREATE TABLE playYouTubeVideo (
> key TEXT NOT NULL,
> speed FLOAT NOT NULL,
>
> CONSTRAINT key CHECK(length(key) == 1),
> CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
>
> PRIMARY KEY(key)
> );
>
>
> Say I want to rotate a part:
> - The value by key '1' becomes the value by key '2'.
> - The value by key '2' becomes the value by key '3'.
> - The value by key '3' becomes the value by key '4'.
> - The value by key '4' becomes the value by key '5'.
> - The value by key '5' becomes the value by key '6'.
>
> I suppose that I need to do this programmatically, or can this be
> done with SQL?
>
> And optionally also:
> - The value by key '1' becomes the value by key '5'.
>
> --
> Cecil Westerhof
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Confidentiality notice: This e-mail is intended solely for use of the
individual or entity to which it is addressed and may contain information that
is proprietary, privileged, company confidential and/or exempt from disclosure
under applicable law. If the reader is not the intended recipient or agent
responsible for delivering the message to the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited. If you have received this communication
in error, please notify the sender by reply e-mail or collect telephone call
and delete or destroy all copies of this e-mail message, any physical copies
made of this e-mail message and/or any file attachment(s).
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
