Stored procedure for a column with string to be split and inserted/updated in a new table in MySQL

Question

I have a requirement to insert / update rows in a table based on information in the other table. I will illustrate my requirement below by providing an example.

Below table is where I want to update / insert data based on data existing in the table table Name tab1

tab1_id | tab1_question_id | tab1_sectorID | tab1_NC_id

1       | 101              | 11            | 21

Below table is where I want to copy the data and import it to the table tab1

tab2_id | tab2_question_id | tab2_sectorID | tab2_NC_ids |import_complete

1       | 101              | 11            | 21,22,23     | 0
2       | 102              | 11            | 22,23,24     | 0
3       | 103              | 11            | 22,24,25     | 0

The tab2_NC_ids have values of different NC's seperated by commas. Now I am trying to insert these values in tab1 table.
For instance in tab1 question_id 101 and NC_id 21 is already existing. So that row shouldn't be duplicated with same question_id and NC_id
I want to split the NC_ids from tab2 and insert/update each row based on data existing in tab1.

In this question I am not just trying to split the values, but also split them and assign values to the new table with relations kept intact and this has to be done dynamically.

How can I achieve this. I am trying to create a stored procedure in MySQL for the above but haven't found any possible solution because of NC_ids is concatenated with a comma. Any help would be great, thanks.

Hint: normilize dont work with comma separated values in a new table. — Raymond Nijland, Oct 26 '17 at 10:33
Possible duplicate of [Can Mysql Split a column?](https://stackoverflow.com/questions/1096679/can-mysql-split-a-column) — Gord Thompson, Oct 26 '17 at 16:03

score 1 · Accepted Answer · answered Oct 31 '17 at 10:58

I found a possible solution to deal my issue.

First I create a temporary table so that the values split are stored in the temporary tables along with its foreign key, so that this can be later user to join and insert values in the new table.

CREATE TEMPORARY TABLE IF NOT EXISTS `db`.`temp_table` (
  `temp_table_id` INT(5) NOT NULL AUTO_INCREMENT,
  `tab_question_id` INT(5) NULL,
  `tab_sector_id` INT(5) NULL,
  `tab_NC_ids` INT(5) NULL,
   PRIMARY KEY (`temp_table_id`));

The above code will create a temporary table and now based on temporary table I will insert all the split values in the temp table.

INSERT into db.temp_table (tab_question_id,tab_NC_id)
     (select  tab2_question_id,tab_sector_id,tab2_NC_id, 
     from db.tab1 a, db.tab2 b
     where b.import_complete = 0
     and find_in_set(a.tab1_id, replace(b.tab2_NC_ids, ' ', '')));

Now I have got the data split and entered in my temp table. Now I can use join and insert data to tab1 table.

INSERT INTO db.tab1(tab1_question_id, tab1_sector_id. tab1_NC_id)
(Select tNC.tab_question_id, tNC.tab_sector_id, tNC.tab_NC_id
from db.temp_table tNCO);

Once the insert is done I remove the temporary table

 DROP TABLE temp_table;

Stored procedure for a column with string to be split and inserted/updated in a new table in MySQL

1 Answers1