Completely generic solution. You change the direction of rotation by changing the SQL that builds your temp table ...
sqlite> create table x (key integer primary key, value); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> insert into x values (random(), random()); sqlite> .head on sqlite> .mode column select * from x order by key; key value -------------------- -------------------- -6374565986553047082 -6292999241545120883 -5989898834901854520 -9081225235206840749 -5612633422423030496 -3498480116044899177 -3719342152283010731 -4268175217960688953 -2423950719408034905 -3377215796687069970 367353711932362007 3900645503222593618 883226292009397075 -1973921941627299252 1707896441609026036 -2722166238737751675 6339993451314418730 -8055191930500241295 8898575339909083958 7883859772702047363 select oldkey, newkey, (select value from x where key = newkey) as value from (select key as oldkey, coalesce((select min(x1.key) from x as x1 where x1.key > x.key), (select min(key) from x)) as newkey from x order by key) as xx; oldkey newkey value -------------------- -------------------- -------------------- -6374565986553047082 -5989898834901854520 -9081225235206840749 -5989898834901854520 -5612633422423030496 -3498480116044899177 -5612633422423030496 -3719342152283010731 -4268175217960688953 -3719342152283010731 -2423950719408034905 -3377215796687069970 -2423950719408034905 367353711932362007 3900645503222593618 367353711932362007 883226292009397075 -1973921941627299252 883226292009397075 1707896441609026036 -2722166238737751675 1707896441609026036 6339993451314418730 -8055191930500241295 6339993451314418730 8898575339909083958 7883859772702047363 8898575339909083958 -6374565986553047082 -6292999241545120883 begin immediate; create temporary table rotate as select oldkey, newkey, (select value from x where key = newkey) as value from (select key as oldkey, coalesce((select min(x1.key) from x as x1 where x1.key > x.key), (select min(key) from x)) as newkey from x order by key) as xx; update x set value = (select value from temp.rotate where oldkey=x.key); drop table temp.rotate; commit; select * from x order by key; key value -------------------- -------------------- -6374565986553047082 -9081225235206840749 -5989898834901854520 -3498480116044899177 -5612633422423030496 -4268175217960688953 -3719342152283010731 -3377215796687069970 -2423950719408034905 3900645503222593618 367353711932362007 -1973921941627299252 883226292009397075 -2722166238737751675 1707896441609026036 -8055191930500241295 6339993451314418730 7883859772702047363 8898575339909083958 -6292999241545120883 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >Sent: Monday, 22 January, 2018 17:19 >To: SQLite mailing list >Subject: Re: [sqlite] Can this be done with SQLite > >2018-01-23 1:02 GMT+01:00 Keith Medcalf <kmedc...@dessus.com>: > >> >> Part of the problem is going to be that you have not defined the >problem >> sufficiently for a "solution" to be proposed. Based on your >somewhat silly >> example one can deduce the following constraints: >> >> With respect to "key": >> - this is TEXT (UTF-8 or something else)? >> - you specify check(length(key)) == 1 do you mean: >> - one character in some encoding (key between 0 and >0xffffffffffffffff) >> - one byte? (ie, ord(key) between 0 and 255) >> - something else entirely? >> - is it contiguous? >> - if not contiguous what are the rules defining the non- >contiguousness? >> > >I would like a general solution. So the type of key is not defined >and it >is not necessary to be contiguous. > > > > >> - what is the "rotation order" based on? >> - next arithmetic value, upper wraps to lower? >> - next used key (by some collation order? Which collation >order?) >> - based on "used" values? >> - based on "entire domain"? >> > >Rotation is either up or down. In my example it was up. (In my >perception.) > >Order is just the default order. > > > > >> The problem and its solution is rather simple, once you define >problem to >> be solved with sufficient specificity to permit a solution. >> >> Your "example" below does not provide sufficient referents to >generate a >> solution that is cohesive over any problem domain other than that >covered >> by the example, and your referential constraints are inadequate to >ensure >> integrity for your limited example. >> > >I think I can solve it generally. I will look into it and share it >here. > > > > >> >-----Original Message----- >> >From: sqlite-users [mailto:sqlite-users- >> >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >> >Sent: Monday, 22 January, 2018 13:30 >> >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