UPDATE test set province =
CASE
when SUBSTRING(id_card,1,2)='11' then '北京'
when SUBSTRING(id_card,1,2)='12' then '天津'
when SUBSTRING(id_card,1,2)='13' then '河北'
when SUBSTRING(id_card,1,2)='14' then '山西'
when SUBSTRING(id_card,1,2)='15' then '内蒙古'
when SUBSTRING(id_card,1,2)='21' then '辽宁'
when SUBSTRING(id_card,1,2)='22' then '吉林'
when SUBSTRING(id_card,1,2)='23' then '黑龙江'
when SUBSTRING(id_card,1,2)='31' then '上海'
when SUBSTRING(id_card,1,2)='32' then '江苏'
when SUBSTRING(id_card,1,2)='33' then '浙江'
when SUBSTRING(id_card,1,2)='34' then '安徽'
when SUBSTRING(id_card,1,2)='35' then '福建'
when SUBSTRING(id_card,1,2)='36' then '江西'
when SUBSTRING(id_card,1,2)='37' then '山东'
when SUBSTRING(id_card,1,2)='41' then '河南'
when SUBSTRING(id_card,1,2)='42' then '湖北'
when SUBSTRING(id_card,1,2)='43' then '湖南'
when SUBSTRING(id_card,1,2)='44' then '广东'
when SUBSTRING(id_card,1,2)='45' then '广西'
when SUBSTRING(id_card,1,2)='46' then '海南'
when SUBSTRING(id_card,1,2)='50' then '重庆'
when SUBSTRING(id_card,1,2)='51' then '四川'
when SUBSTRING(id_card,1,2)='52' then '贵州'
when SUBSTRING(id_card,1,2)='53' then '云南'
when SUBSTRING(id_card,1,2)='54' then '西藏'
when SUBSTRING(id_card,1,2)='61' then '陕西'
when SUBSTRING(id_card,1,2)='62' then '甘肃'
when SUBSTRING(id_card,1,2)='63' then '青海'
when SUBSTRING(id_card,1,2)='64' then '宁夏'
when SUBSTRING(id_card,1,2)='65' then '新疆'
when SUBSTRING(id_card,1,2)='71' then '台湾'
when SUBSTRING(id_card,1,2)='81' then '香港'
when SUBSTRING(id_card,1,2)='91' then '澳门'
END
