1

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!

Community
  • 1
  • 1
Peder Wessel
  • 646
  • 1
  • 9
  • 23
  • 2
    You can only execute *one statement at a time* with that function. Don't use the multi-statement command, it's nothing but trouble. Why not supply a values list like `(?,?),(?,?)`, prepare a single statement and execute it several times, or use a CSV file and `LOAD DATA INFILE` to import it en-masse? Remember `;` only has meaning to the `mysql` command-line tool, it's the default delimiter, but the MySQL driver has no such concept, you must package up individual statements. – tadman Oct 29 '16 at 19:34
  • 1
    Also table names and column names get escaped with backticks `\`` and strings get either single or double quotes by default. You generally only need to escape column/table names that conflict with [MySQL reserved keywords](http://dev.mysql.com/doc/refman/5.7/en/keywords.html) or have irregular characters in them like a space, everything else can be left as-is. – tadman Oct 29 '16 at 19:35

2 Answers2

3

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`)
Haleemur Ali
  • 26,718
  • 5
  • 61
  • 85
1

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

obe
  • 7,378
  • 5
  • 31
  • 40