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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof Sent: Monday, January 22, 2018 4:37 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Can this be done with SQLite 2018-01-22 21:38 GMT+01:00 David Raymond <david.raym...@tomtom.com>: > 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:sqlite-users-boun...@mailinglists.sqlite.org] > 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 > 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 > -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users