CODEWITHSUNDEEP
mysqlsqlnode.jsinsert-update
kecman
kecman

Reputation: 853

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 }

Upvotes: 3

Views: 2562

Answers (2)

John
John

Reputation: 11

Make sure the valuesArray is not empty as well! That was giving me a headache

Upvotes: 0

kecman
kecman

Reputation: 853

answer by @Solarflare:

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

Upvotes: 4

Related Questions