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?

Answer 1

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.