Search code examples
mysqlsqlnode.jsinsert-update

INSERT ON DUPLICATE KEY UPDATE multiple rows at once

I'm using node.js and mysql. I'm trying to insert multiple rows with one query, and if the rows with that primary key already exist, update it.

PK is exchange + currency1 + currency2.

But only one row gets inserted (instead of five).

Here is my valuesArray:

  [ [ 4, 'BTC', 'IDR', 10440000, 10391000 ],
  [ 4, 'BTC', 'MYR', 2380, 2095 ],
  [ 4, 'BTC', 'ZAR', 11216, 11201 ],
  [ 4, 'BTC', 'SGD', 1100, 1093 ],
  [ 4, 'BTC', 'NGN', 403500, 402701 ] ]

and here is my query:

connection.query("INSERT INTO rates (exchange,currency1,currency2,buyrate,sellrate) VALUES(?) ON DUPLICATE KEY UPDATE buyrate=VALUES(buyrate), sellrate=VALUES(sellrate)", valuesArray, function (err) {
    });

I've tried changing my query to this (just adding [ ] around valuesArray):

connection.query("INSERT INTO rates (exchange,currency1,currency2,buyrate,sellrate) VALUES(?) ON DUPLICATE KEY UPDATE buyrate=VALUES(buyrate), sellrate=VALUES(sellrate)", [valuesArray], function (err) {
        });

but then I get this error:

{ [Error: ER_OPERAND_COLUMNS: Operand should contain 1 column(s)]
  code: 'ER_OPERAND_COLUMNS',
  errno: 1241,
  sqlState: '21000',
  index: 0 }
Solution

  • answer by @Solarflare:

    Use ...VALUES ? ON DUPLICATE... (without brackets) and [valuesArray] (with brackets).