mysql--根据birth计算age并更新所有字段

1：根据身份证号sfzh补充birth

UPDATE patient set birth=cast(substring(sfzh,7,8) as date)

2：根据birth计算age并修改所有

UPDATE patient AS A INNER JOIN (SELECT patid,TIMESTAMPDIFF(YEAR,birth,NOW()) as newage FROM patient) AS B ON A.patid=B.patid SET A.age=B.newage