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 }
answer by @Solarflare:
Use ...VALUES ? ON DUPLICATE...
(without brackets) and [valuesArray]
(with brackets).