mysql json链接表_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;