CODEWITHSUNDEEP
phpmysqlsql
user4077608
user4077608

Reputation:

Insert values from A table to A table

I have one table in mysql that stores menu items. Structure is as below

Table: menu_list_hed

id    menu_name    shop_id    menu category
--------------------------------------------
 1     Breakfast      12          Veg
 2     Dinner         12          Veg
 .... Many more

Now, i have another shop which has same menu but shop_id(13) is different. How can i copy same values again and make table look like as below

id    menu_name    shop_id    menu category
--------------------------------------------
 1     Breakfast      12          Veg
 2     Dinner         12          Veg
 .... Many more
 25    Breakfast      13          Veg
 26    Dinner         13          Veg
 .... Many more

I tried exporting SQL from phpmyadmin but that will take lots of time as i have to manually change the shop_id for each SQL statement.

Upvotes: 1

Views: 77

Answers (3)

Manil Liyanage
Manil Liyanage

Reputation: 255

Try the following, have a sub query of to the same table to match the shop_id. Hope at least this structure helps you

INSERT INTO shop(id , menu_name, shop_id, 'menu category')

SELECT MAX(id) + 1 , menu_name ,
(SELECT MAX((shop_id)) +1 FROM menu_list_hed h1 where h1.menu_name = h2.menu_name), menu category)
FROM menu_list_hed h2;

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can use insert with select and provide your shop_id in select statement 

INSERT INTO menu_list_hed
    (`menu_name`, `shop_id`, `menu category`)
select `menu_name`, '13' AS `shop_id`, `menu category`
from menu_list_hed where shop_id = 12
;

Demo

Upvotes: 2

Nitin Kaushal
Nitin Kaushal

Reputation: 211

Write query like this 

INSERT INTO destination_table ( 
      Field_1, 
      Field_2, 
      Field_3) 
SELECT Field_1, 
      Field_2, 
      Field_3 
      FROM source_table;

Upvotes: 1

Related Questions