Insert mysql query results into database column with corrensponding ids

Question

I'm trying to store some data from a temporary query into a table, but it doesn't work.

Suppose we have the following data

Table_A

Submission_id	Salesman	Customer name	Hobby	Carline	Fuel	Marketing_data
1	salesman1	customer1	hobby1	car1,car2	Fuel1	hobby1,car1,car2,Fuel1
2	salesman2	customer2	hobby1,hobby2	car1	Fuel2	hobby1,hobby2,car1,Fuel2

And the radio Button/checkbox value table: Table_B

option_value	option_name
hobby1	Football
hobby2	Golf
car1	SUV
car2	Cabrio
Fuel1	Gas
Fuel2	Hybrid

I've written the following code trying to match the options stored in the previous table against the column "Marketing_data" to obtain a new column:

SELECT GROUP_CONCAT(DISTINCT c0.option_name)Marketing_data
FROM Table_A d
JOIN Table_B c0 ON FIND_IN_SET(c0.option_value, d.Marketing_data)
GROUP BY d.Marketing_data;

And the resulting column is composed as follow:

Marketing_data
Football, SUV, Cabrio, Gas
Football, Golf, SUV, Hybrid

Now, what I'm trying to do is to insert the last column into the first table (Table_A). I tried with INSERT INTO command and I've also tried to generate another table containing the Submission_id field, but nothing worked. Have you got any suggestions? Many Thanks to everyone who will try to help me.

Answer 1

It ais not a good ides to have a comma separated column in your database see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad

But you can make following

CREATE TABLE Table_A
    (`Submission_id` int, `Salesman` varchar(9), `Customer name` varchar(9), `Hobby` varchar(13), `Carline` varchar(9), `Fuel` varchar(5), `Marketing_data` varchar(24))
;
    
INSERT INTO Table_A
    (`Submission_id`, `Salesman`, `Customer name`, `Hobby`, `Carline`, `Fuel`, `Marketing_data`)
VALUES
    (1, 'salesman1', 'customer1', 'hobby1', 'car1,car2', 'Fuel1', 'hobby1,car1,car2,Fuel1'),
    (2, 'salesman2', 'customer2', 'hobby1,hobby2', 'car1', 'Fuel2', 'hobby1,hobby2,car1,Fuel2')
;

CREATE TABLE Table_B
    (`option_value` varchar(6), `option_name` varchar(8))
;
    
INSERT INTO Table_B
    (`option_value`, `option_name`)
VALUES
    ('hobby1', 'Football'),
    ('hobby2', 'Golf'),
    ('car1', 'SUV'),
    ('car2', 'Cabrio'),
    ('Fuel1', 'Gas'),
    ('Fuel2', 'Hybrid')
;

UPDATE 
Table_A a
SET `Marketing_data` = (SELECT GROUP_CONCAT(DISTINCT c0.option_name)Marketing_data
FROM (SELECT * FROM Table_A) d
JOIN Table_B c0 ON FIND_IN_SET(c0.option_value, d.Marketing_data)
 WHERE a.`Submission_id` = d.`Submission_id`
 GROUP BY d.Marketing_data)

SELECT * FROM Table_A;

Submission_id | Salesman  | Customer name | Hobby         | Carline   | Fuel  | Marketing_data          
------------: | :-------- | :------------ | :------------ | :-------- | :---- | :-----------------------
            1 | salesman1 | customer1     | hobby1        | car1,car2 | Fuel1 | Cabrio,Football,Gas,SUV 
            2 | salesman2 | customer2     | hobby1,hobby2 | car1      | Fuel2 | Football,Golf,Hybrid,SUV

db<>fiddle here