nodejs mysql bulk INSERT on DUPLICATE KEY UPDATE

Question

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?

score 3 · Answer 1 · answered May 19 '17 at 20:07

3

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'

answered May 19 '17 at 20:07

Gerardo Rosciano

901
5
11

1

Thanks, unfortunately this will only update one single entry instead of the whole array. – Jakob Abfalter May 21 '17 at 17:23
1

@JakobAbfalter did you found any solution for this? – Vinod Kumar Marupu Dec 18 '19 at 19:24

score 3 · Answer 2 · answered Aug 07 '17 at 17:02

3

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]

answered Aug 07 '17 at 17:02

mxcihak

31
2

This is a correct answer. `sql_data` should be placed inside another array according to this post https://stackoverflow.com/a/14259347/2311651 – Pavlo28 Dec 14 '17 at 14:52

nodejs mysql bulk INSERT on DUPLICATE KEY UPDATE

2 Answers2

Linked