CODEWITHSUNDEEP
mysqlbatch-processing
Peder Wessel
Peder Wessel

Reputation: 656

MYSQL: Batch insert/update statements in single query

I need to do batch MYSQL insert/updates. I got batch insert statement to work, but when the insert comes as multiple one liners it does not.. Similarly I have not been able to generate a batch update. Please see examples below.

Batch insert statement works

$sql = "INSERT INTO `test` (`somefield`) VALUES ('test', 'test');";
db::statement($sql);

Multiple separate insert statements NOT working

$sql = "INSERT INTO `test` (`somefield`) VALUES ('test'); INSERT INTO `test` (`somefield`) VALUES ('test');";
db::statement($sql);

SQLSTATE[42000]: Syntax error or access violation: 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 'INSERT INTO test (somefield) VALUES ('test')' at line 1 (SQL: INSERT INTO test (somefield) VALUES ('test'); INSERT INTO test (somefield) VALUES ('test');)

Batch update statement not working

$sql = "INSERT INTO 'flights' (`id`, `airline`) VALUES ('142832', 'BA') ON DUPLICATE KEY UPDATE `airline`=VALUES(`airline`);"
db::statement($sql);

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 ''flights' (id, airline) VALUES ('142832', 'BA') ON DUPLICATE KEY UP' at line 1

Reviewed multiple Stackoverflow posts - but I am getting something wrong

Multiple insert statements - Multiple SQL Update Statements in single query

Batch update statement - Multiple Updates in MySQL

Would appreciate help on this - thanks!

Upvotes: 1

Views: 8902

Answers (2)

Haleemur Ali
Haleemur Ali

Reputation: 28253

This is weird. at a quick glance it appears that the batch statement is wrong, which is supposedly working.

A semantically correct batch statement would have the brackets separate each row of data, ie. like this:

INSERT INTO test 
VALUES 
  ('test1')
, ('test2')
, ('test3');

The separate insert statements look fine, however, your database driver might not support multiple statements in its statement method (most don't, AFAIK). The work around would be to start a transaction from your client, loop through the array of statements and execute. Then when all the statements execute, commit if there were no errors, or roll back the transaction. The first option is faster though.

The update statement doesn't work because the tablename flights is quoted using single-quotes. If you want to quote schema / table / column identifiers, use back-ticks, and reserve single-quotes for string values & dates, as you have done elsewhere in the same query. It is only necessary to escape a database element name if it is a reserved word, but naming database elements things like 'into', 'user', etc. is bad practice and should be avoided.

INSERT INTO flights (`id`, `airline`) 
VALUES 
('142832', 'BA') 
ON DUPLICATE KEY UPDATE 
airline=VALUES(`airline`)

Upvotes: 3

obe
obe

Reputation: 7808

Try to unquote flights, or quote it with back ticks in your last query (the batch update statement).

Upvotes: 1

Related Questions