How to update multiple columns using array of datas with a prepared statement?

Question

I have 2 arrays :

columns = ['column1', 'column2'];
data = ['data1', 'data2'];

I'd like to update the table using a prepared query:

conn.query('UPDATE table SET ?? = ? WHERE id = ?', [columns, data, id], 
function(err, info){

Excepted sql query :

UPDATE table SET column1 = 'data1', column2 = 'data2' WHERE id = 10

But I get something that looks like :

UPDATE table SET 'column1', 'column2' = 'data1', 'data2' WHERE id = 10

This feature works well for select or insert but it seems not working for update queries. Any thoughts on how I can get this work ?

Answer 1

From node-mysql docs, about escaping query values, we have this:

Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'

, so it won't work the way you expect.

But, in the docs we also have this:

Objects are turned into key = 'val' pairs. Nested objects are cast to strings.

with an example:

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

So, in order to do what you want, the best option IMO, is to convert your arrays into an object like:

{
  column1: 'data1',
  column2: 'data2'
}