0

I have two tables test1 and test2. The CodeNames column in Test 1 contains comma-separated text. The ID column in test2 is a foreign key. I want to split the text from test1 CodeNames column and update the CodeName column in test2 table with ID wise.

There are more records like this. I have given one example.

I have provided the script files below and added before and after images.

CREATE TABLE Test1
(
   Id    int PRIMARY KEY AUTO_INCREMENT,
   CodeNames varchar(300)
);
CREATE TABLE Test2
(
   Id    int 
,
   CodeName varchar(10),
   constraint Fk_Test1 foreign KEY(Id) REFERENCES test1(Id)
);
INSERT INTO test1 (
   Id
  ,CodeNames
) VALUES (
   1, 'A,AA,AAA,AB,ABB'
);
INSERT INTO test2 VALUES (1,'A'),
(1,'AA'),
(1,'AAA'),
(1,'AB'),
(1,'ABB');

Before

After

your help would be much appreciated.

Thanks. EDIT Please open my question. Because according to the data I have, one answer is applied correctly. But the MySQL version I have doesn't support json_table function.

Nandu
  • 103
  • 9
  • as mysql has no such internal function, you can choose one of the duplicates, but you should read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – nbk Jun 22 '23 at 06:49

0 Answers0