How do I make an update with an array in node-mysql

Question

I'm using nodejs-mysql, trying to update a bunch of rows at once.

So, I've tried the following code, with little luck:

var sql = "UPDATE users SET Gender=?, Super=? WHERE Name=?";
var valueArray = [];
for (var idx = 0; idx < data.length; idx++) {
    var char = data[idx];
    valueArray.push([char[4], char[5], char[1]]);
}

connection.query(sql, [valueArray], function(err, rows) {
});

I'm doing a similar syntax (with the array) when I insert, and that works fine.

So what is wrong with doing it this way, for an update? When I execute this, I get a syntax error :(

Should I be doing this in some other way?

Answer 1

As you said it works with INSERT. node-mysql library converts INSERT statement with nested array as you described into one statement, e.g.:

var valueArray = [['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h', 'i']];
var sql = "INSERT INTO users SET Gender=?, Super=? WHERE Name=?";
connection.query(sql, valueArray, function(err, rows) {
    // do something...
});

converts into:

INSERT INTO users
    (Gender, Super, Name)
VALUES
    ('a', 'b', 'c'),
    ('d', 'e', 'f'),
    ('g', 'h', 'i');

However to update multiple entities in one statement is not so easy in sql. You can either query database for each entry in loop, or construct one big statement e.g.:

var valueArray = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'];
var sql = "UPDATE users SET Gender=?, Super=? WHERE Name = ?;"
        + "UPDATE users SET Gender=?, Super=? WHERE Name = ?;"
        + "UPDATE users SET Gender=?, Super=? WHERE Name = ?;"

in this case you need to allow multiple statements in your connection.