私信  •  关注

Vince

Vince 最近回复了
4 年前
回复了 Vince 创建的主题 » MySQL基于json select查询更新多个表列值

实际上,你的问题是正确的。您只需要在子查询中添加别名,子查询才能正常工作。就像这样

UPDATE xp_guru_listings CROSS JOIN
(SELECT 
JSON_EXTRACT(json, '$.agencyLicense') AS cea_no, 
JSON_EXTRACT(json, '$.district') AS district, 
JSON_EXTRACT(json, '$.details."Type"') AS property_type,
RIGHT(JSON_EXTRACT(json, '$.details."Type"'),9) AS listing_type 
from xp_guru_listings) as x
set cea_no = cea_no, 
district = district, 
property_type = property_type, 
listing_type = listing_type;