How to expand a comma delimited field from one table and copy it into multiple fields in a second table which uses the same unique id

0
votes

I have table1 with a field named subjects which is comma delimited with a unique id for each item. 

id1's subjects field contains this information: apple, banana, tomato, melon

id2's subjects field contains this information: pineapple,seed,jelly,fish,eel

Some fields contain as little as one subject and some contain as many as 8 subjects, all separated by commas.

I have table2 which contains 8 fields called cat1, cat2, cat3, cat4, cat5, cat6, cat7, cat8.

I am trying to use the below SQL statement to copy subjects field from table1, break it apart by commas and enter it into the 8 possible cat fields in table2 but I keep getting this error message: "A new statement was found, but no delimiter between it and the previous one. (near "SELECT" at position 68)" . Please assist with correcting my code.

UPDATE table2 VALUES (cat1,cat2,cat3,cat4,cat5,cat6,cat7,cat8) SELECT SUBSTRING_INDEX(subjects,',',1) AS cat1, SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',2),',',-1) AS cat2, SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',3),',',-1) AS cat3, SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',4),',',-1) AS cat4, SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',5),',',-1) AS cat5, SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',6),',',-1) AS cat6, SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',7),',',-1) AS cat7, SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',8),',',-1) AS cat8 FROM table1 WHERE id=table1.id;

Thanks.

2
phpmysqlsubstring
Welcome to Stack Overflow. It doesn't work is not a useful description of your problem. Please edit your question to provide more information about what's wrong.If you take off the INSERT part of the query leaving just the SELECT, what result do you get? – O. Jones
It’s very simple. Don’t have a comma delimited field, or don’t bother using an RDBMS. – Strawberry

2 Answers

1
votes

This is the correct solution:

UPDATE table2 t2 JOIN table1 t1 ON t2.id = t1.id
SET 
  t2.cat1=SUBSTRING_INDEX(t1.subjects,',',1) 
, t2.cat2=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',2),',',-1)
, t2.cat3=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',3),',',-1)
, t2.cat4=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',4),',',-1)
, t2.cat5=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',5),',',-1)
, t2.cat6=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',6),',',-1)
, t2.cat7=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',7),',',-1)
, t2.cat8=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',8),',',-1)

The problem is that if subject8 is blank, this JOIN copies subject7 and re-enters it into subject8, which gives tons of repeated subjects. Some items only have one subject and some have as many as 8 subjects.

Also, is there a way I could LIMIT the join to 10 rows so that I can test it out and make sure it works before I run the join on 18 million rows?

0
votes

If table2 exists and has data to be matched from table1, then it is an update query you need.

UPDATE table2 t2 JOIN table1 t1 ON t2.id = t1.id
SET 
  t2.cat1=SUBSTRING_INDEX(t1.subjects,',',1) 
, t2.cat2=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',2),',',-1)
, t2.cat3=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',3),',',-1)
, t2.cat4=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',4),',',-1)
, t2.cat5=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',5),',',-1)
, t2.cat6=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',6),',',-1)
, t2.cat7=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',7),',',-1)
, t2.cat8=SUBSTRING_INDEX(SUBSTRING_INDEX(t1.subjects,',',8),',',-1)

If table 2 does not have the data in it then, "insert into" does not the table for you in MySQL, You need to create the table then insert into it.

CREATE TABLE Table2
    (id int,cat1 varchar(20), cat2 varchar(20), cat3 varchar(20), cat4 varchar(20), cat5 varchar(20), cat6 varchar(20), cat7 varchar(20), cat8 varchar(20))
;

INSERT INTO table2 (cat1,cat2,cat3,cat4,cat5,cat6,cat7,cat8) 
SELECT id
    , SUBSTRING_INDEX(subjects,',',1) AS cat1
    , SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',2),',',-1) AS cat2
    , SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',3),',',-1) AS cat3
    , SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',4),',',-1) AS cat4
    , SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',5),',',-1) AS cat5
    , SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',6),',',-1) AS cat6
    , SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',7),',',-1) AS cat7
    , SUBSTRING_INDEX(SUBSTRING_INDEX(subjects,',',8),',',-1) AS cat8 
FROM table1