Insert Comma Separated values through MySQL query

Question

I have one table (in phpmyadmin) with the following fields and structure:

Table 1
id  | sponsor
1   | -1 
2   | 1  
3   | 1  
4   | 2  
5   | 4  
6   | 4

Now i want to insert data from above table into new table as below:

Table 2
id  | children
1   | 2,3
2   | 4 
3   |   
4   | 5,6
5   |   
6   |

Actually this is Tree structure, which i have saved in mysql database.
I have already written a script in php but as there are more then 100K rows in table 1 so its taking too much time. Please tell me an efficient sql query to do this task quickly.

Justin · Accepted Answer · 2012-11-14 12:23:21Z

4

Query:

SQLFIDDLE Example

SELECT 
t1.id,
(SELECT group_concat(id separator ', ')
  FROM table1 t2
   WHERE t2.sponsor = t1.id) AS children
FROM table1 t1
GROUP BY t1.id

Result:

| ID | CHILDREN |
-----------------
|  1 |     2, 3 |
|  2 |        4 |
|  3 |   (null) |
|  4 |     5, 6 |
|  5 |   (null) |
|  6 |   (null) |

Insert Statement:

INSERT INTO table2
    SELECT 
    t1.id,
    (SELECT group_concat(id separator ', ')
      FROM table1 t2
       WHERE t2.sponsor = t1.id) AS children
    FROM table1 t1
    GROUP BY t1.id

answered Nov 14, 2012 at 12:23

Justin

9,7336 gold badges39 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:22:20Z

2

This is similar to @Justin's answer but uses a left join instead of a correlated subquery:

INSERT INTO Table2 (id, children)
SELECT
  sp.id,
  GROUP_CONCAT(ch.id) AS children
FROM Table1 sp
LEFT JOIN Table1 ch ON sp.id = ch.sponsor
GROUP BY t1.id
;

A demonstration of the SELECT statement's result can be found (and played with) at SQL Fiddle (the schema having been borrowed from Justin).

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Nov 14, 2012 at 13:18

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Comments

Frildoren · Accepted Answer · 2012-11-14 11:31:08Z

1

One of your SELECT elements should be a GROUP_CONCAT(...) as column, which will concatenate those values separated with commas. If you want to filter by one of those values, you can use GROUP BY -whatever- HAVING find_in_set( -number- , column )

answered Nov 14, 2012 at 11:31

Frildoren

2433 silver badges10 bronze badges

1 Comment

Awn Ali Over a year ago

Can you please write query here ?

xelber · Accepted Answer · 2012-11-14 11:31:38Z

0

See if the following helps

INSERT INTO table2
SELECT sponsor, GROUP_CONCAT(id)
FROM table1
GROUP BY id

answered Nov 14, 2012 at 11:31

xelber

4,6633 gold badges30 silver badges38 bronze badges

1 Comment

Awn Ali Over a year ago

This is giving following output: -1 1, 1 2, 1 3 , 2 4 , 4 5 , 4 6 ,

Collectives™ on Stack Overflow

Insert Comma Separated values through MySQL query

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related