MySQL With Node Express, UPDATE using a Map

Question

So I have a table of users which includes the columns 'user_id' and 'points'.

Now, I want to update multiple rows using the Map/Array [{id: 1, points: 2}, {id: 2, points: 4}, etc...].
And another problem is, I want to ADD to the points values, so building a query-string using a for loop is somewhat more challenging as I need the values beforehand that way.

A (non-working) query (just for understanding the question) would be something like this:

UPDATE users SET points = points + map[i].points WHERE id = map[i].id;

As I mentioned, without the need to add to the value, I would create a String beforehand, and use something like

UPDATE users SET (points) VALUES(x,y,z) WHERE id IN(a, b, c);

But even then, I can't be sure the updates are in the same order as the Map...

edit: I would like to do it in 1 query:)

Any ideas?

EDIT
bagz_man's answer did exactly what I needed. Here it is with the minor syntax errors fixed:

var sql = 'UPDATE users SET points = CASE id';
var i = 0;
var idString='';
map.forEach(function(item){
    sql += ' WHEN '+item.id+' THEN points + '+item.points;
    idString += item.id.toString();
    if(i < map.length-1){
        idString +=', ';
    }
    i++;
});
sql += ' END WHERE id IN('+idString+') ;';

bagz_man · Accepted Answer · 2014-05-27 01:21:31Z

1

var mysql      = require('mysql');
var connection = mysql.createConnection({ user: 'root', database: 'test'});

var map = [{id:1, points:2}, {id:2, points:4}, ....];

map.forEach(function(item){
  connection.query('SELECT points FROM users WHERE id='+item.id, function(err, rows) {
    if(err)
      console.log(err);
    if(rows.length > 0){
      var newPoints = parseInt(rows[0].points) + parseInt(item.points);
      connection.query('UPDATE users SET points='+newPoints+' WHERE id='+item.id, function(err, result){
        console.log(result.affectedRows+' rows updated!');
      });
    }
    else
      console.log('No user with id:'+item.id);
  });
});

EDIT : In above code, Node.js will execute the queries in each loop asyncronously, which is acceptable in terms of execution time.

But if you would like to make it only 1 query operation, you should do some string work.

var mysql      = require('mysql');
var connection = mysql.createConnection({ user: 'root', database: 'test'});

var map = [{id:1, points:2}, {id:2, points:4}, ....];

var sql = 'UPDATE users SET points = CASE id';
var i = 0;
var idString='';
map.forEach(function(item){
    sql += ' WHEN '+item.id+' THEN points + '+item.points;
    idString += item.id.toString();
    if(i < map.length-1)
      idString +=', ';
}
sql += ' END WHERE id IN('+idString+')';

connection.query(sql, function(err, result){
  if(err)
    console.log(err);
  console.log(result.affectedRows+' rows updated!');
});

edited May 27, 2014 at 1:21

answered May 26, 2014 at 16:33

bagz_man

5752 gold badges8 silver badges21 bronze badges

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

4 Comments

guy_m Over a year ago

thx, but that would make tons of queries. I would like to do it in one query..edited question to clear that out.

bagz_man Over a year ago

Remember that the queries in each loop are executed asyncronously in Node.js. But if you really want to do it 1 query-only. I just edited the answer.

guy_m Over a year ago

thank you!! works perfectly! there were minor syntax issues, I edited my question with your answer and the fixes:)

guy_m Over a year ago

btw, I added the i++ without testing, as it looks like it's needed, unless the forEach magically increments it:D

Collectives™ on Stack Overflow

MySQL With Node Express, UPDATE using a Map

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related