0

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.

1 Answer 1

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

3
  • Hi, I tried to use the script in my SQL database, but the result is the following: #3105 - The value specified for generated column 'datimarketing' in table '1459630_form_1' is not allowed. Dec 30, 2020 at 14:17
  • And this is the query that I've done: UPDATE 1459630_form_1 a SET 'datimarketing' = (SELECT GROUP_CONCAT(DISTINCT c0.option_name)datimarketing FROM (SELECT * FROM 1459630_form_1) d JOIN 1459630_field_options c0 ON FIND_IN_SET(c0.option_value, d.datimarketing) WHERE a.submission_id = d.submission_id GROUP BY d.datimarketing) SELECT * FROM 1459630_form_1; Dec 30, 2020 at 14:22
  • @FrancescoMarcucci Please amke your own dbfiddel to show your problem, comments are not the right place for it
    – nbk
    Jan 3, 2021 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.