mysql - Multiple UPDATE's in a single query -

in database of mine, there couple of indexes got updated , save year worth of data need rearange these indexes.

so, have table called floattableold has field needs updated called tagindex.

i have old indexes stored in tagtableold , new ones stored in tagtable, share field tagname, , need replace tagtableold.tagindex tagtable.tagindex.

i'm having bit of trouble because want in single query. i've got is:

update `floattableold`  set `floattableold`.`tagindex` =  (     select `relacao`.`newtag`     (         select  `tagtable`.`tagname`,             `tagtableold`.`tagindex` `oldtag`,             `tagtable`.`tagindex` `newtag`         `tagtable`         inner join `tagtableold`         on `tagtable`.`tagname` = `tagtableold`.`tagname`     ) `relacao`     `floattableold`.`tagname` = `relacao`.`oldtag` ) `floattableold`.`tagindex` =  (     select `floattableold`.`tagindex`      `floattableold` ) 

however following error:

error 1093 (hy000): can't specify target table 'floattableold' update in clause 

can me this? can't understand error.

you use update join this. if understand correctly:

update floattableold fto join        tagtableold tto        on fto.tagindex = tto.tagindex join        tagtable tt        on tt.tagname = tto.tagname     set fto.tagindex = tt.tagindex;