UPDATE: I added an CONSTRAINT DEFERRABLE to the create table, which renders LanX comment obsolete. Sorry about that./UPDATE
This is not for Oracle MySQL, I think (I didn't find the necessary functionality). This is just for fun, therefore Postgres :P
This won't work for you but you expressed some interest (or what I took to be interest) in the CB so here goes:
Below is the (p)sql-inside-bash that I cobbled together; I inserted the database-output in the script ( /* commented out */ ) for better reading.
#!/bin/sh t=ctetree echo " \set insert_location 2.5 begin transaction; drop table if exists $t ; create table $t ( f_node_id int primary key , f_parent_id int , f_name text , f_sort float , constraint tree_parent_sort_uniq_idx unique (f_parent_id, f_sort) d +eferrable ); insert into $t values (142, 2, 'B06', 1) , (143, 2, 'B2L', 2) , (144, 2, 'B2M', 3) , (145, 2, 'B2N', 4) , (146, 2, 'B2O', 5) ; \echo -- base data: table $t; /* -- base data: f_node_id | f_parent_id | f_name | f_sort -----------+-------------+--------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 144 | 2 | B2M | 3 145 | 2 | B2N | 4 146 | 2 | B2O | 5 (5 rows) */ insert into $t values (394, 2, '*Inserted 1st*', :insert_location); update $t set f_sort = floor(f_sort)+1 where f_sort >= :insert_locatio +n; /* THIS BETTER REPLACED BY... see my later posts downstream */ \echo -- new begin state, after 1st insert and renumbered: table $t order by f_sort; /* -- new begin state, after 1st insert and renumbered: f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 394 | 2 | *Inserted 1st* | 3 144 | 2 | B2M | 4 145 | 2 | B2N | 5 146 | 2 | B2O | 6 (6 rows) */ with inserted as ( insert into $t values (395, 2, '*Inserted 2nd*', :insert_location) returning f_sort ) update $t set f_sort = floor(f_sort)+1 where f_sort > (select f_sort +from inserted); \echo -- after 2nd insert table $t order by f_sort; /* -- after 2nd insert f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 395 | 2 | *Inserted 2nd* | 2.5 394 | 2 | *Inserted 1st* | 4 144 | 2 | B2M | 5 145 | 2 | B2N | 6 146 | 2 | B2O | 7 (7 rows) */ update $t set f_sort = floor(f_sort)+1 where f_sort <> floor(f_sort); \echo -- after 2nd insert - renumbered table $t order by f_sort; /* -- after 2nd insert - renumbered f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 395 | 2 | *Inserted 2nd* | 3 394 | 2 | *Inserted 1st* | 4 144 | 2 | B2M | 5 145 | 2 | B2N | 6 146 | 2 | B2O | 7 (7 rows) */ -- -- remove rows 2 and 3, and insert them after the row where f_sort +is 5. -- \set location_to_insert 5 \echo -- initial: table $t order by f_sort; /* -- initial: f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 395 | 2 | *Inserted 2nd* | 3 394 | 2 | *Inserted 1st* | 4 144 | 2 | B2M | 5 145 | 2 | B2N | 6 146 | 2 | B2O | 7 (7 rows) */ create temp table _t_$t ( like $t including all ); \echo -- intermediary 1, empty temp table (to receive deleted rows): table _t_$t order by f_sort; /* -- intermediary 1, empty temp table (to receive deleted rows): f_node_id | f_parent_id | f_name | f_sort -----------+-------------+--------+-------- (0 rows) */ \echo -- with deleted -> d. rows inserted into tmp table with deleted as ( delete from $t where f_sort in (2,3) returning * ) insert into _t_$t select * from deleted; \echo -- intermediary 2 (filled with the deleted rows): table _t_$t order by f_sort; /* -- intermediary 2 (filled with the deleted rows): f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 143 | 2 | B2L | 2 395 | 2 | *Inserted 2nd* | 3 (2 rows) */ \echo -- with updated -> rows inserted in main table with updated as ( update _t_$t set f_sort = :location_to_insert + f_sort / 1000 returning * ) insert into $t table updated; create temp table _t as select *, row_number() over () as new_number from (select * from $t order by f_sort) f order by new_number ; \echo -- semi-ready: table _t order by f_sort; /* -- semi-ready: f_node_id | f_parent_id | f_name | f_sort | new_number -----------+-------------+----------------+--------+------------ 142 | 2 | B06 | 1 | 1 394 | 2 | *Inserted 1st* | 4 | 2 144 | 2 | B2M | 5 | 3 143 | 2 | B2L | 5.002 | 4 395 | 2 | *Inserted 2nd* | 5.003 | 5 145 | 2 | B2N | 6 | 6 146 | 2 | B2O | 7 | 7 (7 rows) */ update $t t set f_sort = _t.new_number from _t where _t.f_node_id = t.f_node_id --> join and _t.new_number <> t.f_sort --> avoid writes ; \echo -- ready: table $t order by f_sort; /* -- ready: f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 394 | 2 | *Inserted 1st* | 2 144 | 2 | B2M | 3 143 | 2 | B2L | 4 395 | 2 | *Inserted 2nd* | 5 145 | 2 | B2N | 6 146 | 2 | B2O | 7 (7 rows) */ rollback; -- clean up " | psql -Xqa | less -iSR
UPDATE: slightly tweaked UPDATEs
In reply to Re: [OT] MySQL recalibrating a sort-index
by erix
in thread [OT] MySQL recalibrating a sort-index
by LanX
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |