Jump to content


Photo

Mysql insert multiple data issue


  • Please log in to reply
4 replies to this topic

#1 laxi

laxi

    Advanced Member

  • Members
  • PipPipPip
  • 53 posts

Posted 18 September 2013 - 10:52 PM

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)
VALUES



(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

Error

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

dungpt29

    Member

  • Members
  • PipPip
  • 29 posts

Posted 19 September 2013 - 01: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 - 01:22 AM.


#3 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,217 posts
  • LocationCheshire, UK

Posted 19 September 2013 - 03:21 AM

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

INSERT INTO table (a,b,) VALUES
(1, x),
(2, y),
(3, z);

Semicolon terminates query


Edited by Barand, 19 September 2013 - 03: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

vinny42

    Advanced Member

  • Members
  • PipPipPip
  • 414 posts

Posted 19 September 2013 - 03: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 JaysonDotPH

JaysonDotPH

    Advanced Member

  • Members
  • PipPipPip
  • 177 posts
  • LocationPhilippines

Posted 19 September 2013 - 08:08 PM

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

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com