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?

What is console.log(inspect(valueArray))? I am thinking your values are wrong, or out of order. — Gary
– Gary, Commented Dec 9, 2015 at 22:39

madox2 · Accepted Answer · 2015-12-09 23:05:40Z

3

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.

edited Dec 9, 2015 at 23:05

answered Dec 9, 2015 at 22:40

madox2

52.2k21 gold badges106 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SefaUn Over a year ago

is there any way to update my data with one sql query ?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related