how to insert same columns from second columns values mysql

Question

table 1

id | sub1           | sub2                | sub3
1  | KK Nagar       | Alwartirunagar      |   
1  | Valasaravakkam | Nandambakkam        | Ramapuram
1  | Porur          | Meenambakkam        | Ashok Nagar
1  | Anna Nagar     | Kodambakkam         | Nungambakkam
2  | West Mambalam  | Virugambakkam       | T Nagar
2  | KK Nagar       |                     |

how to insert values in a new table in single column like sub1,sub2 and sub3 values group by id

Normalize this data or it is going to be a nightmare. See [Junction Tables](http://stackoverflow.com/a/32620163) — Drew, Oct 05 '16 at 05:09
I want all data in a new table from this table..like that id | sub 1 | KK Nagar 1 | Alwartirunagar 1 | Porur — Chitra, Oct 05 '16 at 05:12
I know you like that. Because as a human you can visualize that. Unfortunately your users won't for the UX and performance. And you won't because your life will be one with find_in_set and tablescans and no use of indexes. And data modification will be nearly impossible. No one would architect it like that with a data architect. But be our guest. — Drew, Oct 05 '16 at 05:15
Your data isn't normalized and you're basically asking for an unbounded pivot the `sub1` column. — Tim Biegeleisen, Oct 05 '16 at 05:16
Not to mention that as depicted there is no Primary Key to speak of. Maybe you have an unshown auto_increment. But your strategy is to just find the next 2 that doesn't have filled up slots and plop one in there. You are using a high performance beast of a RDBMS not some hokey spreadsheet or index card contraption. My recommendation is to spend some days on the basics of DB design then come back — Drew, Oct 05 '16 at 05:22
I want like that id | subvalues 1 | KK Nagar 1 | Alwartirunagar 1 | Valasaravakkam 1 | Nandambakkam 1 | Ramapuram 1 | Meenambakkam 1 | Ashok Nagar 1 | Kodambakkam 1 | Porur 1 | Nungambakkam 1 | Anna Nagar 2 | West Mambalam 2 | KK Nagar 2 | Virugambakkam 2 | T Nagar — Chitra, Oct 05 '16 at 05:27

Mukesh Swami · Accepted Answer · 2016-10-05T06:21:52.830

1

Just use group concat with group by option like below - Your main table -

| id | sub1 | sub2 | sub3 |

Now when you run a query - select id,group_concat(sub1,'|',sub2) from t1 group by id ; It will give you data like below

| id | group_concat(sub1,',',sub2,',',sub3) | | 1 | kk nagar,Alwartirunagar,,Valasaravakkam,Nandambakkam,Ramapuram,Porur,Meenambakkam,Ashok Nagar,Anna Nagar,Kodambakkam,Nungambakkam | | 2 | West Mambalam,Virugambakkam,T Nagar,KK Nagar,, |

Now just insert this data in another table by below query insert into t2 select id,group_concat(sub1,',',sub2,',',sub3) from t1 group by id ;

Now you will get data in t2 like below

| id | grouped_sub |
| 1 | kk nagar,Alwartirunagar,,Valasaravakkam,Nandambakkam,Ramapuram,Porur,Meenambakkam,Ashok Nagar,Anna Nagar,Kodambakkam,Nungambakkam |

| 2 | West Mambalam,Virugambakkam,T Nagar,KK Nagar,,

Hope this will help you.

Note :- As question requirement are different - Query - insert into t2 select * from (select id ,sub1 as subject from t1 union select id,sub2 as subject from t1 union select id,sub3 as subject from t1 ) temp where subject !='' order by id ;

| id | grouped_sub | +------+----------------+ | 1 | Nungambakkam | | 1 | Alwartirunagar | | 1 | Ramapuram | | 1 | Porur | | 1 | Kodambakkam | | 1 | kk nagar | | 1 | Nandambakkam | | 1 | Ashok Nagar | | 1 | Anna Nagar | | 1 | Valasaravakkam | | 1 | Meenambakkam | | 2 | West Mambalam | | 2 | T Nagar | | 2 | KK Nagar | | 2 | Virugambakkam |

edited Oct 05 '16 at 06:21

answered Oct 05 '16 at 05:44

Mukesh Swami

415
2
11

i am using group_concat and insert into 2nd table... i want to seperate rows..not like that . – Chitra Oct 05 '16 at 05:53
Yes sure ,Share your confusion – Mukesh Swami Oct 05 '16 at 05:54
without comma in a same columns – Chitra Oct 05 '16 at 05:55
Please share outout of one row what you want . – Mukesh Swami Oct 05 '16 at 05:57
id | subvalues 1 | KK Nagar 1 | Alwartirunagar 1 | Valasaravakkam 1 | Nandambakkam 1 | Ramapuram 1 | Meenambakkam 1 | Ashok Nagar 1 | Kodambakkam 1 | Porur 1 | Nungambakkam 1 | Anna Nagar 2 | West Mambalam 2 | KK Nagar 2 | Virugambakkam 2 | T Nagar – Chitra Oct 05 '16 at 05:59
can u understand my requested output? – Chitra Oct 05 '16 at 06:00
Is this only 2 rows with every column concated with id or these are total 15 rows – Mukesh Swami Oct 05 '16 at 06:06
insert into t2 select * from (select id ,sub1 as subject from t1 union select id,sub2 as subject from t1 union select id,sub3 as subject from t1 ) temp where subject !='' order by id ; Please mark this answer and upvote if this works for you , For me it worked – Mukesh Swami Oct 05 '16 at 06:19
what is the meaning of temp – Chitra Oct 05 '16 at 06:26
It is just a temporary table name given . – Mukesh Swami Oct 05 '16 at 06:28
SELECT campaign_id, sub1 AS sub_locations FROM view_loc WHERE sub1 != "" UNION SELECT campaign_id, sub2 AS sub_locations FROM view_loc WHERE sub2 != "" UNION SELECT campaign_id, sub3 AS sub_locations FROM view_loc WHERE sub3 != "" order by campaign_id – Chitra Oct 05 '16 at 06:52
this code is working but ..when adding insert into command its not working..can u guide me – Chitra Oct 05 '16 at 06:53
I added the insert command above and it is working fine for me , What error you are getting – Mukesh Swami Oct 05 '16 at 06:58
#1136 - Column count doesn't match value count at row 1 – Chitra Oct 05 '16 at 09:54
INSERT INTO new_tbl SELECT * FROM(SELECT campaign_id,sub1 AS sub_locations,admin1 AS admin_locations,country AS country,locality AS locality FROM view_loc WHERE sub1!="" AND admin1!="" UNION SELECT campaign_id,sub2 AS sub_locations,admin2 AS admin_locations,country AS country,locality AS locality FROM view_loc WHERE sub2!="" AND admin2!="" UNION SELECT campaign_id,sub3 AS sub_locations,admin3 AS admin_locations,country AS country,locality AS locality FROM view_loc WHERE sub3!="" AND admin3!="" ORDER BY campaign_id) temp – Chitra Oct 05 '16 at 09:57
Do your new_tbl contain same no of column which you are getting = 5 – Mukesh Swami Oct 05 '16 at 10:03
i am not understand ...my table (new_tbl) feilds are id,campaign_id,sub_locations,admin_locations,locality,country – Chitra Oct 05 '16 at 10:05
Your new_tbl having 6 column while you are giving it 5 columns only so you are getting This error . Do like this INSERT INTO new_tbl (campaign_id,sub_locations,admin_locations,locality,country) SELECT * FROM(SELECT campaign_id – Mukesh Swami Oct 05 '16 at 10:09
provide column name atleast Like my above comment ,Please mark my answer solved if this help you . – Mukesh Swami Oct 05 '16 at 10:13
Please mark this answer as closed ,It will give me confidence to solve more queries – Mukesh Swami Oct 05 '16 at 10:19
iam using like that INSERT INTO new_tbl( campaign_id, sub_locations, admin_locations, country, locality ) SELECT * FROM ( – Chitra Oct 05 '16 at 10:43
Please mark this answer as solves by clicking on "checkbox mark " just on left side of my answer . – Mukesh Swami Oct 05 '16 at 10:53

how to insert same columns from second columns values mysql

1 Answers1