How to properly use ON DUPLICATE KEY UPDATE in Node.js MySQL

Question

I'm using Node.js to push values to a MySQL table like:

     for (var i = 0; i < data.length; i++) {
        flattenedData.push([data[i].id, data[i].adult, data[i].backdrop_path, JSON.stringify(data[i].genre_ids), data[i].original_language, data[i].original_title, data[i].overview, data[i].popularity, data[i].poster_path, data[i].release_date, data[i].title, data[i].video, data[i].vote_average, data[i].vote_count]);
        //console.log(flattenedData);
      }                                                      
      db.query("INSERT INTO movies (id, adult, backdrop_path, genre_ids,  original_language, original_title, overview, popularity, poster_path, release_date, title, video, vote_average, vote_count ) values ?", [flattenedData], function (err, result) {
        if (err) {
          throw err;
        }
        else {
          console.log('data inserted' + result);
        }
     });

I want to add ON DUPLICATE KEY UPDATE to the query, but I keep getting syntax errors, can anyone show me the proper way?

Possible duplicate of [On Duplicate Key Update same as insert](https://stackoverflow.com/questions/14383503/on-duplicate-key-update-same-as-insert) — Elie Asmar, Dec 19 '17 at 19:02
@ElieAsmar I am using JavaScript, the link in your comment is referring to SQL only. — kennypowers, Dec 19 '17 at 19:05

score 8 · Accepted Answer · answered Dec 19 '17 at 19:05

I'm going to short this to three columns, and assume id is the only unique column.

db.query("INSERT INTO movies (id, adult, backdrop_path) VALUES (?, ?, ?)
 ON DUPLICATE KEY UPDATE adult=VALUES(adult), backdrop_path=VALUES(backdrop_path)",
flattenedData, function (err, result) {
...

This means if the insert results in a duplicate on the primary/unique column (id), then copy the other columns from the values you tried to insert in the VALUES clause into their respective column, overriding what their value was previously in the existing row.

There's no shortcut for that; you have to spell out all such column assignments.

I'm pretty sure the argument to query() for parameters should be an array, but it looks like your flattenedData is already an array since you're pushing to it. So I don't think you need to put it in square-brackets.

kenadian · Answer 2 · 2018-09-28T21:31:21.783

I was confused by how to get this to work in a react/redux app and eventually came to the "correct" method.

My implementation required me to update one field value per record for an arbitrary number of records in a table with 21 fields.

If you are passing data as an array structure it like [['dataString',666.66],['dataString2',666666.66],['dataString3',666666666.66]] and then make sure you pass this whole thing as an array to the query function. See itemQtyData in my code sample below.

Another thing that tripped me up was the use of brackets around the values replacement string. I didn't need them. The examples I looked at showed implementations that needed them. I also only used a single ? for all the values. So instead of using (?,?) to represent the values in the query, which didn't work, I used ?.

I found it unnecessary to supply all the field names and the corresponding values for the table. MySQL will warn you if fields don't have a default value. I haven't found this to be an issue in this case.

You can console.log the formatted sql in SqlString.format function in the file node_modules/sqlstring/lib/SqlString.js. I found this useful to see exactly why the query wasn't working and to have something that I could plug into MySQL Workbench to mess around with.

Edit: You can also do this console.log(connection.query(yourQuery, [someData], callback)) and you get the sql and lot's more when the function executes. Might make more sense than adding console.log calls to the module code.

Hope this helps!

let itemQtyData = order.map(item => {
    return [
      `${item.id}`,
      `${Number(item.products_quantity - Number(item.quantity_to_add))}`
    ];
  });

const updateQtyQuery =`INSERT INTO products (products_id, products_quantity) VALUES ? ON DUPLICATE KEY UPDATE products_quantity=VALUES(products_quantity)`;

connectionOSC.query(
  updateQtyQuery,
  [itemQtyData],
  (error, results, fields) => {
    if (error) throw error;

    const response = {
      statusCode: 200,
      headers: {
        "Access-Control-Allow-Origin": "*"
      },
      body: saleId,
      response: results,
      isBase64Encoded: false
    };
context.succeed(response);
});

How to properly use ON DUPLICATE KEY UPDATE in Node.js MySQL

2 Answers2