MySQL基于json select查询更新多个表列值

我刚在数据库中创建了4个名为 cea_no , district , property_type 和 listing_type . 我想将基于select查询的结果插入到我添加的新列中。选择查询结果来自行 json 它是从 json格式 数据。我怎么能做到?我尝试了一些方法,结果成功了,问题是它插入了一个新行,我的数据现在翻了一番。提前谢谢

我的桌子结构。

+------------------+------------+------+-----+---------------------+-------------------------------+
| Field            | Type       | Null | Key | Default             | Extra                         |
+------------------+------------+------+-----+---------------------+-------------------------------+
| id               | int(11)    | NO   | PRI | NULL                | auto_increment                |
| json             | mediumtext | NO   |     | NULL                |                               |
| property_name    | text       | NO   |     | NULL                |                               |
| property_address | text       | NO   |     | NULL                |                               |
| price            | text       | NO   |     | NULL                |                               |
| listed_by        | text       | NO   |     | NULL                |                               |
| contact          | text       | NO   |     | NULL                |                               |
| cea_no           | text       | NO   |     | NULL                |       EMPTY  for now          |
| district         | text       | NO   |     | NULL                |       EMPTY  for now          |
| property_type    | text       | NO   |     | NULL                |       EMPTY  for now          |
| listing_type     | text       | NO   |     | NULL                |       EMPTY  for now          |
| update_time      | timestamp  | NO   |     | current_timestamp() | on update current_timestamp() |
+------------------+------------+------+-----+---------------------+-------------------------------+

我试过的问题

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;

正确的样本结果

+------------------------------+----------+------------------------+--------------+
| cea_no                       | district | property_type          | listing_type |
+------------------------------+----------+------------------------+--------------+
| "CEA: R017722B \/ L3009740K" | "(D25)"  | "Apartment For Sale"   | For Sale"    |
| "CEA: R016023J \/ L3009793I" | "(D25)"  | "Condominium For Sale" | For Sale"    |
| "CEA: R011571E \/ L3002382K" | "(D25)"  | "Condominium For Sale" | For Sale"    |
| "CEA: R054044J \/ L3010738A" | "(D21)"  | "Apartment For Sale"   | For Sale"    |
| "CEA: R041180B \/ L3009250K" | "(D09)"  | "Condominium For Sale" | For Sale"    |
+------------------------------+----------+------------------------+--------------+

这是我要在新列中插入的值。

编辑: 我试过这个问题但没用

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
)
set cea_no = cea_no, 
district = district, 
property_type = property_type, 
listing_type = listing_type;