INSERT ON DUPLICATE KEY UPDATE multiple rows at once

Question

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 }

Use `...VALUES ? ON DUPLICATE...` (without brackets) and `[valuesArray]` (with brackets). — Solarflare, Dec 09 '16 at 08:31
Use **VALUES ?** instead of **VALUES(?)** and see : http://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js — Bernd Buffen, Dec 09 '16 at 21:44
@BerndBuffen yes I saw that question before I posted mine, but it didn't help me. Solarflare solved my problem. I just don't know how to promote his answer to be accepted answer for this question. Thanks for your effort anyway! — kecman, Dec 09 '16 at 23:37
http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 — e4c5, Dec 11 '16 at 01:06
@Solarflare please answer this question with your answer from comment so I can mark it as correct and close the question topic. — kecman, Dec 11 '16 at 01:38
@kecman This was just a pointer to the correct syntax (which I was sure someone else asked before, so I didn't post it as an answer). Glad it helped, but my answer would look nearly word for word like the one Bernd linked. This is actually a duplicate (and should be deleted); since you saw it before and it didn't help, that might be true for someone else and he might find your question more helpful, so feel free to self-answer with the solution (that would have helped you more than the other answer). Might still get flagged (and deleted) as a duplicate though. Or delete it yourself. — Solarflare, Dec 11 '16 at 21:43

score 4 · Accepted Answer · answered Dec 12 '16 at 00:03

4

answer by @Solarflare:

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

answered Dec 12 '16 at 00:03

kecman

813
3
14
34

INSERT ON DUPLICATE KEY UPDATE multiple rows at once

1 Answers1