Ok, so you're looking for a "rotate" sort of thing? (The schema with a text key with length of 1 made me think it wasn't going to get too big)
Are the keys all integers then? All positive? Continuous? begin transaction; create temp table t (key int primary key, speed real); insert into t select key, (select t1.speed from playYouTubeVideo as t1 where t1.key = foo.key % 5 + 1) from playYouTubeVideo; update playYouTubeVideo set speed = (select speed from t where key = playYouTubeVideo.key); drop table t; commit; -----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 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users