I would like to pass data from a table with several columns to another table in a single column

Question

good afternoon / night, let me tell you, I have a database with several tables and one of them is obsolete since I am trying to pass it to another. I put you in situation. I have the user_accounts table with the following format. "image below"

I would like to know if that information can be selected and inserted into another table "users" with this format. "link below"

It would be a search for the identifier and the same update for the identifier.

Thanks in advance.

JSON is horribly difficult to work with in MySQL. Why do you want to convert to that format? — Barmar, Aug 09 '21 at 23:35

score 0 · Accepted Answer · answered Aug 09 '21 at 23:39

0

Use JSON_OBJECTAGG()

UPDATE users AS u
JOIN (
    SELECT identifier, JSON_OBJECTAGG(name, money) AS accounts
    FROM user_accounts
    GROUP BY identifier
) AS a ON u.identifier = a.identifier
SET u.accounts = a.accounts

answered Aug 09 '21 at 23:39

Barmar

741,623
53
500
612

I would like to pass data from a table with several columns to another table in a single column

1 Answers1