Node.js mysql query syntax issues UPDATE WHERE

Question

I trying to update some info in MYSQL DB, but I'm not sure of how to do it with in node.js. This is the mysql driver I'm using https://github.com/felixge/node-mysql

What I have so far

connection.query('SELECT * FROM users WHERE UserID = ?', [userId], function(err, results) {
if (results[0]) {
if (results[0].Name!=name) {
console.log(results[0].Name);
connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});
}
console.log(results[0].UserID);
}
});

Everything works except...

connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});

In PHP I would have this...

mysql_query("UPDATE users SET Name='".$_GET["name"]."' WHERE UserID='".$row['UserID']."'");

I'm not sure what I'm doing wrong, But I'm positive that the issue is here

connection.query('UPDATE users SET ? WHERE UserID = ?', [userId], {Name: name});

Josh Laird · Accepted Answer · 2019-08-22 10:08:36Z

52

The answer by ruakh isn't entirely correct, you can use the SET ? feature with another ?.

The syntax is:

connection.query('UPDATE users SET ? WHERE UserID = ?', [{ Name: name }, userId])

edited Aug 22, 2019 at 10:08

Josh Laird

7,2447 gold badges41 silver badges72 bronze badges

answered Oct 14, 2013 at 10:57

Bala Clark

1,52413 silver badges19 bronze badges

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

Comments

ruakh · Accepted Answer · 2016-04-29 17:02:30Z

50

[Note (added 2016-04-29): This answer was accepted, but it turns out that there is a reasonable way to use SET ?. For details, see Bala Clark's answer on this same page. —ruakh]

From the code for Connection.prototype.query() and Connection.createQuery(), it's clear that you can only pass in a single values object. I don't see where the code for the special SET ? behavior is defined — it's clearly not in SqlString.formatQuery() — but if it uses SqlString.objectToValues(), then I guess there's no way to use it together with another ?.

I think the best approach is to just dispense with the neat SET ? feature, and write either of these:

connection.query('UPDATE users SET Name = ? WHERE UserID = ?', [name, userId])

connection.query('UPDATE users SET Name = :Name WHERE UserID = :UserID',
                     {UserID: userId, Name: name})

but if you really want to use SET ?, I suppose you could write this:

connection.query('UPDATE users SET ? WHERE UserID = :UserID',
                     {UserID: userId, Name: name})

which would update both UserID and Name; unless you have a trigger, this should be O.K., in that it's updating UserID to the value it already had anyway. But it's kind of disconcerting, and I don't recommend it.

edited Apr 29, 2016 at 17:02

answered Feb 21, 2013 at 1:33

ruakh

185k29 gold badges292 silver badges324 bronze badges

2 Comments

ruakh Over a year ago

@MarcioJasinski: Thanks for your edit suggestion, but it really changes this answer in a way that will invalidate other answers on this page. I'll see if I can find a more graceful way to add that information . . .

DrDirk Over a year ago

You should mention that in order for your :Name and :UserId implementation to work one needs to include a custom-format configuration.

Marcel · Accepted Answer · 2016-05-12 06:40:43Z

22

You can use an array of objects:

connection.query('UPDATE user SET ? WHERE ?', [{ Name: name }, { UserId: userId }])

edited May 12, 2016 at 6:40

answered Apr 2, 2014 at 8:57

Marcel

8,7095 gold badges25 silver badges25 bronze badges

1 Comment

mynameisbutt Over a year ago

how can I use OR in this query after WHERE?

Shubham Dixit · Accepted Answer · 2018-09-22 07:18:17Z

0

This is what worked for me

 var records = 
 [name,email,adhar,ROLE,NOTES,REQUESTFORSUPPLIER,PASSWORD,LOCATION,CREATEDBY,CREATEDON,MODIFIEDBY,MODIFIEDON,mobile];

    var sql="UPDATE flavica_user SET Name=?,EMAIL=?,AADHAR=?,ROLE=?,NOTES=?,REQUESTFORSUPPLIER=?,PASSWORD=?,LOCATION=?,CREATEDBY=?,CREATEDON=?,MODIFIEDBY=?,MODIFIEDON=? WHERE USER_MOBILE=?"
    //var sql="UPDATE `flavica_user` SET ? WHERE ?";
    //this sql updates flavica_user
    con.query(sql,records,(err,result)=>{

      if(err){
        res.json({
          status:400,
          message:err
        })
      }

      else{
        res.json({
          status:200,
          message:result
        })
      }

    })

answered Sep 22, 2018 at 7:18

Shubham Dixit

9,2685 gold badges32 silver badges50 bronze badges

Comments

Ajay · Accepted Answer · 2021-04-16 02:59:44Z

let query_1 =  `UPDATE reindexing_status SET success="Failure" where status="In Progress"`;
await updateValues(db_connection,query_1);
 
if(lambdaInvokeStatusCode === 200)

{
   let query_1 =  `UPDATE reindexing_status SET success="Success" where status="In Progress"`;
   await updateValues(db_connection,query_1);
   
   let query_2 =  `UPDATE reindexing_status SET end_time='${indexedAt}' where status="In Progress"`;
   await updateValues(db_connection,query_2);
   
   let query_3 =  `UPDATE reindexing_status SET status="Complete" where status="In Progress"`;
   await updateValues(db_connection,query_3);
   

}



async function updateValues(db_connection,sql) {
await new Promise((resolve, reject) => {
    db_connection.query(sql, function (err, result) {
        if (err) {
            console.log(err);
            reject(err);
        }
        else {
           
            console.log("RDS Updated Successfully",result );
            resolve("RDS URL Column: " + JSON.stringify(result));
        }
    });
 
});
}

akshat chawla · Accepted Answer · 2022-12-23 11:02:42Z

0

connection.query('UPDATE users SET Column_name = ? WHERE UserID = ?', [update_Value, userId]);

edited Dec 23, 2022 at 11:02

answered Dec 22, 2022 at 8:55

akshat chawla

5310 bronze badges

Collectives™ on Stack Overflow

Node.js mysql query syntax issues UPDATE WHERE

6 Answers 6

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related