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
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.
