3

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 ?

Alex Netkachov
  • 13,172
  • 6
  • 53
  • 85
Bobby Shark
  • 1,074
  • 2
  • 17
  • 33

2 Answers2

4

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'
}
Rodrigo Medeiros
  • 7,814
  • 4
  • 43
  • 54
  • 1
    And for those who don't know how to convert arrays into objects: `var somearray = [ 'data1','data2','data3','etc']; newObject = Object.assign({}, somearray); console.log(newObject);` The primary difference between these being that the object has key'd index while the array does not. – John Jul 19 '17 at 19:22
0

Just to clarify, since after Googling for ages I didn't find an exact example to show what I was looking for. Here is the code which hopefully is what Bobby Shark found out, since I don't think 'SET ??' works.

To UPDATE multiple columns (but not necessarily all) for one row, without typing up every column in the query, but passing an object of {column_name1: 'new_foo', column_name2: 'new_bar', column_name2: 'new_baz'}

(using body-parser to get object of form data)

var blog = req.body.blog; // object as described above
var sql = "UPDATE blog SET ? WHERE id = ?";

connection.query(sql, [blog, req.params.id], function(err, updatedBlog){
if(err){
      console.log(err);
    } else {
      res.redirect("/blogs/" + req.params.id);
    }
  });

This post by Bala Clark helped (though we've read it in the docs 10 times!). Hope this helps to see example code of object with multiple column updates (name/value pairs). Again found no specific examples in many sites.

Matthew
  • 13
  • 4