sql setting xml property through a loop -

trying desperately combine 2 simple answers need.

sql loop , set properties

sql set xml value

set @i := 0; select *, @i := @i + 1 set xml = updatexml(xml,'comic/pagenumber', '<pagenumber>'.@i.'</pagenumber>') `comics` order extractvalue(xml,'comic/pagenumber')+100000 asc 

this close have come, know select / order works separate trying set xml property.

side note: +100000 work around treat value numeric sorting. otherwise 11 < 2 100011 > 100002

i have tried this

set @i := 0; update comics, @i := @i + 1 newpagenumber set xml = updatexml(xml,'comic/pagenumber', '<pagenumber>'.@i.'</pagenumber>') 1 order extractvalue(xml,'comic/pagenumber')+100000 asc 

i think don't know how combine select , update

update comics inner join (   select c.id, @row:=@row+1 rownum   (select @row:=0) x cross join comics c   order extractvalue(xml,'comic/pagenumber')*1.0) y on y.id=comics.id set xml = updatexml(xml,                  'comic/pagenumber',                  concat('<pagenumber>',y.rownum,'</pagenumber>')) ; 

based on test schema , data

create table comics (id int auto_increment primary key, xml text); insert comics select null, '<comic><name>test1</name><pagenumber>7</pagenumber><content>page 5 con</content></comic>'; insert comics select null, '<comic><name>test1</name><pagenumber>3</pagenumber><content>page 6 con</content></comic>'; insert comics select null, '<comic><name>test1</name><pagenumber>5</pagenumber><content>page 7 con</content></comic>';