mysql判断数字是否存在并截取

//通过id匹配 testdate.id=testa.id
//如果有  就赋值num2   比如 id=1    testdata.num2= 10
//如果 没有就看 name 后面有没有数字, 有就截取数字的值  比如  id=5   num2 =50
//如果都没有 就赋值 1
UPDATE testdate a set num2=
(case 
when (select b.name from testa b where b.id=a.id) is not NULL then (SELECT b.name from testa b where b.id=a.id)
when (SELECT b.name from testa b where b.id=a.id) is NULL then 
(case 
when SUBSTR( a.name FROM (locate("-",a.name)+1)) REGEXP '^[0-9]+$' then SUBSTR( a.name FROM (locate("-",a.name)+1))
when SUBSTR( a.name FROM (locate("-",a.name)+1)) not REGEXP '^[0-9]+$' then 1
 end) 
end) where num2 is null