title: INSERT ON DUPLICATE KEY UPDATE multiple rows at once
body: <p>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.</p>
  
  <p>PK is <code>exchange + currency1 + currency2</code>.</p>
  
  <p>But only one row gets inserted (instead of five).</p>
  
  <p>Here is my <code>valuesArray</code>:</p>
  
  <pre><code>  [ [ 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 ] ]
  </code></pre>
  
  <p>and here is my query:</p>
  
  <pre><code>connection.query("INSERT INTO rates (exchange,currency1,currency2,buyrate,sellrate) VALUES(?) ON DUPLICATE KEY UPDATE buyrate=VALUES(buyrate), sellrate=VALUES(sellrate)", valuesArray, function (err) {
      });
  </code></pre>
  
  <p>I've tried changing my query to this (just adding [ ] around valuesArray):</p>
  
  <pre><code>connection.query("INSERT INTO rates (exchange,currency1,currency2,buyrate,sellrate) VALUES(?) ON DUPLICATE KEY UPDATE buyrate=VALUES(buyrate), sellrate=VALUES(sellrate)", [valuesArray], function (err) {
          });
  </code></pre>
  
  <p>but then I get this error:</p>
  
  <pre><code>{ [Error: ER_OPERAND_COLUMNS: Operand should contain 1 column(s)]
    code: 'ER_OPERAND_COLUMNS',
    errno: 1241,
    sqlState: '21000',
    index: 0 }
  </code></pre>