2018-01-22 23:15 GMT+01:00 David Raymond <david.raym...@tomtom.com>: > Ok, so you're looking for a "rotate" sort of thing? >
Yes. ;-) > (The schema with a text key with length of 1 made me think it wasn't going > to get too big) > In this particular case it is a string with length 1, but I am 'always' looking at the general case. > Are the keys all integers then? All positive? Continuous? > In this case yes, but it does not need to be. > 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; > Comes a good end in the right direction, but I am thinking I am going to do it programmatically. Maybe write a general function for it. Everyone thanks for the fast replies. > -----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 > -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
