ORDERS表中DELIVER_ADDRESS字段的JSON格式为
{
"addressZh": "海府一横路",
"areaName": "美兰区",
"areaid": 22469,
"cityName": "海口市",
"cityid": 2121,
"contact": "andy",
"custSeq": "P2018102600000001",
"deliverSeq": "D2018110900000001",
"isDefaultAdd": "Y",
"postcode": "",
"provinceName": "海南",
"provinceid": 23,
"telephone": "176****3369",
"townId": 0
}
####### 使用JSON_EXTRACT 函数,从JSON中读取字段并转成临时表
CREATE TABLE tmp AS SELECT
ORDER_NO,
REPLACE (
JSON_EXTRACT (
DELIVER_ADDRESS,
'$.contact'
),
'"',
''
) AS 'contactName',
REPLACE (
JSON_EXTRACT (
DELIVER_ADDRESS,
'$.telephone'
),
'"',
''
) AS 'contactMobile'
FROM
ORDERS b
######更新联系人姓名字段
UPDATE ORDERS a
SET a.CONTACT_NAME = (
SELECT
contactName
FROM
tmp b
WHERE
a.ORDER_NO = b.ORDER_NO
)
######更新联系人电话字段
UPDATE ORDERS a
SET a.CONTACT_PHONE = (
SELECT
contactMobile
FROM
tmp b
WHERE
a.ORDER_NO = b.ORDER_NO
);
######删除表
drop table tmp;