mysql - 基于json选择查询更新多个表列值
问题描述
我刚刚在我的数据库中创建了 4 个新列,名为cea_no
、和。我想根据我的选择查询将它们的结果插入到我添加的新列中。选择查询结果来自行,它是从数据中提取的。我怎么能做到这一点?我尝试了一些方法并且成功了,问题是它插入了一个新行,我的数据现在翻了一番。district
property_type
listing_type
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;
解决方案
您需要使用 a INNER JOIN
,而不是 aCROSS JOIN
否则您将插入不正确的数据。并且您需要在id
值匹配的适当条件下加入。这应该有效:
update xp_guru_listings x
join (
SELECT id,
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) j ON j.id = x.id
set x.cea_no = j.cea_no,
x.district = j.district,
x.property_type = j.property_type,
x.listing_type = j.listing_type;
请注意,您可以更简单地使用以下部分中的JSON_EXTRACT
公式直接编写:SET
UPDATE
UPDATE xp_guru_listings
SET cea_no = JSON_EXTRACT(json, '$.agencyLicense'),
district = JSON_EXTRACT(json, '$.district'),
property_type = JSON_EXTRACT(json, '$.details."Type"'),
listing_type = RIGHT(JSON_EXTRACT(json, '$.details."Type"'),9)
推荐阅读
- objective-c - 领域非托管 RLMArray
- notepad++ - Notepad++ 替换以数字结尾的文本
- javascript - 如何在laravel中使用点击动态链接调用动态模式弹出?
- linux - 哈希目录结构:它有效吗?会有多少文件和目录?
- python - python问题中的cuda
- elixir - 比较 Ecto 中的静态时间
- java - 有人可以解释这个 if-loop 的方法来删除线性列表中的否定吗?
- android - Volley 增加 ThreadPool 大小 Android
- javascript - 在 javascript 中显示 .json 文件中的单个元素
- javascript - 我们可以将 [(ngModel)] 从 Angular 6 绑定到 JSONEditor