CODEWITHSUNDEEP
mysql
Vijaya Pandey
Vijaya Pandey

Reputation: 4282

Insert data dynamically for combination of different fields in mysql table

I have tbl_vdc table, which contains:

id,zone_id, district_id, vdc_id, details

Here, each zone have different districts and each district have different vdc.

zone_id |  district_id | vdc_id | details
1       |  1           | 1      | pyro   
1       |  1           | 2      | cuit  
1       |  1           | 3      | aden  

1       |  2           | 1      | rtyo   
1       |  2           | 2      | cyan 


2       |  1           | 1      | lity   
2       |  1           | 2      | pais  
2       |  1           | 3      | yeno  
2       |  1           | 4      | zige 


14       |  75           | 1      | moie   
14       |  75           | 2      | hode  
14       |  75           | 3      | gano

and so on upto 14 zones and 75 district.

I need to add one vdc_details N/A for each district where vdc_id must be max(vdc_id)+1 for that district. So, the new table would be like this:

zone_id |  district_id | vdc_id | details

1       |  1           | 1      | pyro   
1       |  1           | 2      | cuit  
1       |  1           | 3      | aden   
1       |  1           | 4      | NA 

1       |  2           | 1      | rtyo   
1       |  2           | 2      | cyan 
1       |  2           | 3      | NA 


2       |  1           | 1      | lity   
2       |  1           | 2      | pais  
2       |  1           | 3      | yeno  
2       |  1           | 4      | zige 
2       |  1           | 5      | NA 

14       |  75           | 1      | moie   
14       |  75           | 2      | hode  
14       |  75           | 3      | gano 
14       |  75           | 4      | NA

For 14 zones and 75 district, I need to add data 75 times which is time consuming and not logical approach.

Is there any way to add dynamically using query?

Upvotes: 0

Views: 48

Answers (1)

peterm
peterm

Reputation: 92805

Try

INSERT INTO tbl_vdc (zone_id, district_id, vdc_id, details)
SELECT zone_id,
       district_id,
       MAX(vdc_id) + 1,
       'N/A'
  FROM tbl_vdc
 GROUP BY zone_id, district_id

Sample output:

| ID | ZONE_ID | DISTRICT_ID | VDC_ID | DETAILS |
-------------------------------------------------
|  1 |       1 |           1 |      1 |    pyro |
|  2 |       1 |           1 |      2 |    cuit |
|  3 |       1 |           1 |      3 |    aden |
| 13 |       1 |           1 |      4 |     N/A |
|  4 |       1 |           2 |      1 |    rtyo |
|  5 |       1 |           2 |      2 |    cyan |
| 14 |       1 |           2 |      3 |     N/A |
|  6 |       2 |           1 |      1 |    lity |
|  7 |       2 |           1 |      2 |    pais |
|  8 |       2 |           1 |      3 |    yeno |
|  9 |       2 |           1 |      4 |    zige |
| 15 |       2 |           1 |      5 |     N/A |
| 10 |      14 |          75 |      1 |    moie |
| 11 |      14 |          75 |      2 |    hode |
| 12 |      14 |          75 |      3 |    gano |
| 16 |      14 |          75 |      4 |     N/A |

Here is SQLFiddle demo

Upvotes: 2

Related Questions