Jump to content


Mysql insert multiple data issue

  • Please log in to reply
4 replies to this topic

#1 laxi


    Advanced Member

  • Members
  • PipPipPip
  • 53 posts

Posted 19 September 2013 - 03:52 AM

Hi everyone,

I am trying to insert multiple data into my mysql database table with the below syntax.

however it only inserts the first row of data and returns a syntax error. Not sure what i am doing wrong...Please advice
INSERT INTO three_drops ( id, tier_one,tier_two, tier_three)

(4, 'CLOTHING', 'MEN', 'Jeans');
(5, 'CLOTHING', 'MEN', 'Cargos');
(6, 'CLOTHING', 'MEN', 'Shorts & 3/4ths');
(7, 'CLOTHING', 'MEN', 'Trousers');


it inserts only 4th item and returns an error for the remaing items


SQL query:

( 5, 'CLOTHING', 'MEN', 'Cargos' ) ;

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5, 'CLOTHING', 'MEN', 'Cargos')' at line 1

Viewing: Dev Shed Forums > Databases >

#2 dungpt29



  • Members
  • PipPip
  • 29 posts

Posted 19 September 2013 - 06:21 AM

SQL syntax:  INSERT INTO table_name (column1,column2,column3,...)VALUES (value1,value2,value3,...); only allows inserting one record per time. 

Edited by dungpt29, 19 September 2013 - 06:22 AM.

#3 Barand


    Sen . ( ile || sei )

  • Gurus
  • 16,091 posts
  • LocationCheshire, UK

Posted 19 September 2013 - 08:21 AM

Use commas, not semicolons, to separate the individual records.

(1, x),
(2, y),
(3, z);

Semicolon terminates query

Edited by Barand, 19 September 2013 - 08:22 AM.


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns


#4 vinny42


    Advanced Member

  • Members
  • PipPipPip
  • 414 posts

Posted 19 September 2013 - 08:26 AM

As a side note: be carefull when inserting multiple values at once in MySQL, some versions don't rollback so when the third set of values fails to insert, the first two may have made it into the table already and are not removed (even though when an insert query fails you'd expect none of the data to be saved).


Test this error on your database before using this method in production. (and ofcourse always use InnoDB and transactions!)

#5 Yohanne


    Advanced Member

  • Members
  • PipPipPip
  • 188 posts

Posted 20 September 2013 - 01:08 AM

Hi laxi.


use array implode to insert multiple of records and from your every text box, create an id to identify array. 

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users