3

Ok here's the thing. I need to insert multiple rows in one column from table A to one row in table B using MySql

Here's an example of what im trying to achieve:

Table A
id | data
1  | name
2  | date
3  | more

From table A, i need to select all the rows in the data column and insert that data in one row of 'data' column of table B like this:

Table B 
id | data
1  | name, date, more

Here is my current code, however it isn't working and shows me an error subquery returns more than one row

INSERT INTO B (data) values (SELECT data from A)

Is there any way i can do this? Please excuse as i am a newbie :P

Thanks in advance!

user1987363
  • 31
  • 5

1 Answers1

1

Use GROUP_CONCAT function

Try this: 

INSERT INTO B (data) 
SELECT GROUP_CONCAT(data) FROM A;
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • Oh that's great thanks! Just one more thing, what if I have to insert data from selected rows of table A? Eg I want the values of the data column of rows with id=1 or id=2 from A to B? thanks in advance! – user1987363 Jan 17 '13 at 14:34
  • Oh it is a part of a problem i have been given by my IT professor to solve. – user1987363 Jan 17 '13 at 14:39
  • @user1987363: use a `WHERE` condition. e.g. `... FROM A WHERE A.id IN (1,2)` – dnagirl Jan 17 '13 at 14:44
  • @user1987363 Don't forgot to accept any of the answer from all answers whichever you find works for you – Saharsh Shah Jan 17 '13 at 18:47