mysql - Adjacency table to nested set conversion -

mysql - Adjacency table to nested set conversion -

i need convert adjacency list nested set in mysql. have found 1 resource on net convert adjacency list nested set using mysql(http://data.bangtech.com/sql/nested_set_treeview.htm). code on same webpage.

create table test.tree (emp char(10) not null, boss char(10)); create table test.personnel( emp char(20) primary key, boss char(20) references personnel(emp), salary decimal(6,2) not null ); insert test.personnel values ('jerry', 'null',1000.00); insert test.personnel values ('bert', 'jerry',900.00); insert test.personnel values ('chuck', 'jerry',900.00); insert test.personnel values ('donna', 'chuck',800.00); insert test.personnel values ('eddie', 'chuck',700.00); insert test.personnel values ('fred', 'chuck',600.00); insert test.tree select emp, boss test.personnel;

i create tree table personnel table. tree table has boss-employee hierarchy. adjacency list. convert nest set, applied code.

begin atomic declare counter integer; declare max_counter integer; declare current_top integer; set counter = 2; set max_counter = 2 * (select count(*) test.tree); set current_top = 1; insert test.stack select 1, emp, 1, null test.tree boss null; delete test.tree boss null; while counter <=(max_counter - 2) loop if exists (select * test.stack s1, test.tree t1 s1.emp = t1.boss , s1.stack_top = current_top) begin -- force when top has subordinates, set lft value insert test.stack select (current_top + 1), min(t1.emp), counter, null test.stack s1, test.tree t1 s1.emp = t1.boss , s1.stack_top = current_top; delete test.tree emp = (select emp test.stack stack_top = current_top + 1); set counter = counter + 1; set current_top = current_top + 1; end else begin -- pop stack , set rgt value update test.stack set rgt = counter, stack_top = -stack_top -- pops stack stack_top = current_top set counter = counter + 1; set current_top = current_top - 1; end if; end loop; end;

mysql workbench shows several syntax errors not remove.

i familiar basic operations of mysql not debug code on own. how remove these errors? plz help. sec source found above operation http://www.sqlservercentral.com/articles/hierarchy/94040/ code in t sql , don't have plenty skills translate mysql.

you should set null , not 'null' in line

insert test.personnel values ('jerry', 'null',1000.00);

correct version:

insert test.personnel values ('jerry', null, 1000.00);

mysql nested-sets adjacency-list