How to use mysql query in forloop with nodejs

Question

I want to update database in for loop with mysql query.

 for(let i=0;i<userarray.length;i++){
console.log(i);
     var sql = "update user set user_status=1 where id=" + userarray[i].id;
    con.query(sql, function(err, result) {
                    if (err) throw err;     
 console.log("Number of row update: " + result.affectedRows);


                });
     }
and the output is:
1
2
3
4
Number of row update:1
Number of row update:0
Number of row update:0
Number of row update:0

Here only one time database is updating.I found async/await is solution but i dont know how to use it.

Looping through each user id and executing one query per user id is highly ineffidient. Construct a single query instead and execute that single query one time. Something like update user set user_status=1 where id in (userarray[0], userarray[1]...) — Sani Huttunen
– Sani Huttunen, Commented Oct 10, 2018 at 5:05

Weedoze · Accepted Answer · 2018-10-10 07:29:25Z

2

If you are using mysql library you should use the parameter to insert the ids

const userarray = [{id:0}, {id:2}, {id:5}];
const idsList = [userarray .map(user => user.id)];
const sql = "UPDATE user SET user_status = 1 WHERE id IN (?)";

con.query(sql, idsList, (err, result) => {
  if(err) throw err; 
  console.log(result);
});

edited Oct 10, 2018 at 7:29

answered Oct 10, 2018 at 6:34

Weedoze

14k2 gold badges38 silver badges65 bronze badges

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

Comments

Felix Fong · Accepted Answer · 2018-10-10 06:22:05Z

0

Maybe you can use the IN operator within the SQL query

let sql = 'update user set user_status = 1 where id IN (';
const userarray = [
  {
    id: 1,
  },
  {
    id: 2,
  },
  {
    id: 3,
  },
];
for(let i = 0; i < userarray.length; i++) {
  const split = i === 0 ? '' : ',';
  sql += `${split}${userarray[i].id}`;
}
sql += ')';
console.log(sql)

answered Oct 10, 2018 at 6:22

Felix Fong

1,0051 gold badge10 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to use mysql query in forloop with nodejs

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related