Node.js mysql query syntax issues UPDATE WHERE

[Note (added 2016-04-29): This answer was accepted, but it turns out that there is a reasonable way to use SET ?. For details, see Bala Clark's answer on this same page. —ruakh]


From the code for Connection.prototype.query() and Connection.createQuery(), it's clear that you can only pass in a single values object. I don't see where the code for the special SET ? behavior is defined — it's clearly not in SqlString.formatQuery() — but if it uses SqlString.objectToValues(), then I guess there's no way to use it together with another ?.

I think the best approach is to just dispense with the neat SET ? feature, and write either of these:

connection.query('UPDATE users SET Name = ? WHERE UserID = ?', [name, userId])
connection.query('UPDATE users SET Name = :Name WHERE UserID = :UserID',
                     {UserID: userId, Name: name})

but if you really want to use SET ?, I suppose you could write this:

connection.query('UPDATE users SET ? WHERE UserID = :UserID',
                     {UserID: userId, Name: name})

which would update both UserID and Name; unless you have a trigger, this should be O.K., in that it's updating UserID to the value it already had anyway. But it's kind of disconcerting, and I don't recommend it.


The answer by ruakh isn't entirely correct, you can use the SET ? feature with another ?.

The syntax is:

connection.query('UPDATE users SET ? WHERE UserID = ?', [{ Name: name }, userId])

Tags:

Mysql

Node.Js