我刚在数据库中创建了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;