实际上,你的问题是正确的。您只需要在子查询中添加别名,子查询才能正常工作。就像这样
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;