Update multiple table column values based on json select query

Question

I just created 4 new columns in my database named cea_no, district, property_type and listing_type. I want to insert the result of them based on my select query into the new column that i added. The select query result is from the row json and it is extracted from a json data. How could I achieve that? I tried some methods and it worked, the problem is it inserts a new row and my data is now doubled.

My table structure.

+------------------+------------+------+-----+---------------------+-------------------------------+
| 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() |
+------------------+------------+------+-----+---------------------+-------------------------------+

The query that I tried

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;

Sample result which is correct

+------------------------------+----------+------------------------+--------------+
| 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"    |
+------------------------------+----------+------------------------+--------------+

That is the values that I want to insert in the new columns.

EDIT: I tried this query but it wont work

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;

Nick · Accepted Answer · 2019-12-11 03:05:04Z

1

You need to use an INNER JOIN, not a CROSS JOIN otherwise you will get incorrect data inserted. And you need to join on the appropriate condition which is that the id values match. This should work:

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;

Note that you could write this more simply using the JSON_EXTRACT formulas directly in the SET part of the 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)

edited Dec 11, 2019 at 3:05

answered Dec 11, 2019 at 2:23

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

draw134 Over a year ago

Really sir? why would he get incorrect data by using cross join? please explain to me.

user12110431 Over a year ago

Why would i get incorrect data from using cross join sir?

Nick Over a year ago

Because a cross join joins every row of one table to every row of the other so there's no guarantee which row's data from derived table will be used to update the main table

user12110431 Over a year ago

I tried using your query sir but it returns ` Column 'cea_no' in field list is ambiguous` I just removed the x from the update statement

Nick Over a year ago

@Vince no need to apologise - well, not to me anyway!

|

draw134 · Accepted Answer · 2019-12-11 02:20:10Z

0

Actually, your query is correct. You just need to add an alias to your subquery in order for it to work. Its like this

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;

answered Dec 11, 2019 at 2:20

draw134

1,2194 gold badges44 silver badges100 bronze badges

1 Comment

user12110431 Over a year ago

Ohh I see. So that why it always returns Error Code: 1248. Every derived table must have its own alias . Thanks a lot for this

Collectives™ on Stack Overflow

Update multiple table column values based on json select query

2 Answers 2

14 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related