CODEWITHSUNDEEP
mysql
aika aika
aika aika

Reputation: 51

Insert Select Multiple rows

Hi I am new to mysql I can successfully insert one row but I wanted to insert three row but i been spending hours thinking how if anyone can advice or help me I will really appreciate it thank you

INSERT INTO earning (id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name),

SELECT x_add1,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address1","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address1'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'

but then I wanted to insert in multiple rows I am assigning different id and address

I tried like this but I know its wrong

INSERT INTO earning (id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name),

(id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name),

(id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name)

SELECT x_add1,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address1","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address1'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'


    SELECT x_add2,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address2","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address2'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'


    SELECT x_add13,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address3","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address3'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'

Upvotes: 0

Views: 69

Answers (3)

Erlisar Vasquez
Erlisar Vasquez

Reputation: 460

To insert multiple rows, use this syntax:

INSERT INTO tbl_name
    (column1, column2, column3, ...)
VALUES
    (Value1a, Value2a, Value3a, ...),
    (Value1b, Value2b, Value3b, ...),
    (Value1c, Value2c, Value3c, ...);

Upvotes: 2

Nick
Nick

Reputation: 147146

You can insert all the values by using a UNION on your SELECT queries. Note that the default behaviour for UNION is to remove duplicate rows, so if you want to have them in the table you would need to change UNION to UNION ALL.

INSERT INTO earning (id, dateCreated,
dateModified,
x_t_name,
x_start_date,
x_end_month,
x_address,x_category,x_pic,x_promo_space,x_start_month,x_space_query,x_organizer,x_end_date 
,x_address_query,x_current_pic,x_start_year
,x_end_year
,x_event_title
,x_pix_name)
SELECT x_add1,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address1","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address1'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'
UNION
    SELECT x_add2,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address2","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address2'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'
UNION
    SELECT x_add13,dateCreated,dateModified,"","2018-11-01",
x_end_month,"address3","",x_current_pic,"",x_start_month,"''","", "2018-11-12","'address3'"
,x_current_pic,x_end_year,x_end_year,"",""
  FROM Sale
 WHERE id = 'x1'

Upvotes: 1

Mounir DJEMA
Mounir DJEMA

Reputation: 29

The right syntax is 

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

You can try by adding another statement in the where clause as following 

WHERE id = 'x1' and x_address IN (address1, address2, address3)

Upvotes: 1

Related Questions