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 ?
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'
}