CODEWITHSUNDEEP
mysqlnode.js
Jakob Nielsen
Jakob Nielsen

Reputation: 5198

nodejs mysql bulk INSERT on DUPLICATE KEY UPDATE

I am trying to insert around 1000 rows with one single mysql statement and update the row if the key already exists.

I doing this in nodejs using this module.

My code currently looks like this:

this.conn.query("INSERT INTO summoners VALUES ?" +
    " ON DUPLICATE KEY UPDATE name = VALUES(name), rank = VALUES(rank), points = VALUES(points), satisfyCriteria = VALUES(satisfyCriteria), priority = VALUES(priority)," +
    " recentlyChecked = VALUES(recentlyChecked), hotStreak = VALUES(hotStreak), veteran = VALUES(veteran), freshBlood = VALUES(freshBlood), " +
    " wins = VALUES(wins), losses = VALUES(losses)", sql_data, (err) => {
    if( err ){
        logger.error("Error during summoner insert ", err)
    }
    else {
        cb();
    }
})

sql_data is a nested array. According to the documentation of the libaray :

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

Therefore I thought this should work but currently I am getting this Error

 Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Hy Dag3', '55040464', 'master', 114, true, false, false, false, true, false, 34' at line 1

Debugging the sql looks like this:

'INSERT INTO summoners VALUES \'Hy Dag3\', \'55040464\', \'master\', 114, true, false, false, false, true, false, 343, 279 ON DUPLICATE KEY UPDATE name = VALUES(name), rank = VALUES(rank), points = VALUES(points), satisfyCriteria = VALUES(satisfyCriteria), priority = VALUES(priority), recentlyChecked = VALUES(recentlyChecked), hotStreak = VALUES(hotStreak), veteran = VALUES(veteran), freshBlood = VALUES(freshBlood),  wins = VALUES(wins), losses = VALUES(losses)'

which is not correct.

Could anyone help me out making this work?

Upvotes: 4

Views: 6435

Answers (2)

mxcihak
mxcihak

Reputation: 31

try to put your sql_data array of arrays into another array like this

this.conn.query("...your query", [sql_data], (err) => {...})

so you match question mark in your statement, so if there was another one question mark, it would look like [sql_data, another_variable]

Upvotes: 3

Gerardo Rosciano
Gerardo Rosciano

Reputation: 901

I would try an array of objects

[
 {name:'Hy Dag3', points:'55040464', rank:'master', hotStreak:114,...},
 {name:'Hkj', points:'554064', rank:'novice', hotStreak:14,...}
]

and then

this.conn.query("INSERT summoners SET ? " +
" ON DUPLICATE KEY UPDATE name = VALUES(name), rank = VALUES(rank)...

Because according to doc:

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function (error, results, fields) {
  if (error) throw error;
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

Upvotes: 3

Related Questions