mysql - Update table values from another table using joins? -

i trying update collection of computer hostnames match changed room numbers. hostnames in database formatted fl-itf2106a 2106 being old room number. have list in table has old , new room numbers on same row. have been trying strip non-numerics out of string hostname , join updates table unsuccessfully.

    update computers c inner join updates u          on u.old = (                     select numericonly(c.hostname)                      hostname                        "%fc%"                    )        set c.hostname = concat('classf', u.new); 

numericonly user function removes characters numerics string.

i trying set hostname column equal classf + new room number.

i'd take chances like

    update computers c inner join updates u          on u.old = numericonly(c.hostname)         set c.hostname = concat('classf', u.new)      c.hostname "%fc%";