How to UPDATE a query with different arguments in MySQL

Question

Environment

Node.js, MySQL (using node-mysql library connect)

At first

I could have one argument 'aaa' or two arguments 'aaa' and 'bbb'.

UPDATE user SET email = 'aaa' WHERE uid = 'xxx';
UPDATE user SET email = 'aaa', password = 'bbb' WHERE uid = 'xxx';

My problem

How could I use one query to overcome above cases? Like below ...

var sql = "UPDATE user SET email = ?, password = ? WHERE uid = ?;"
dbclient.query(sql, [email, password, uid], function (err, results) {
    ...
});

If two arguments were defined, variable sql was :

sql = "UPDATE user SET email = '[email protected]', password = 'abcd' WHERE uid = 1;";

If someone argument was undefined, variable sql was :

sql = "UPDATE user SET email = '[email protected]', password = password WHERE uid = 1;";

tgo · Accepted Answer · 2015-03-29 07:21:37Z

7

Please read the documentation, especially the section escaping query values. node-mysql supports some neat use of object serialization.

The following example should be what you want (it uses format, but so does query() internally):

var mysql = require( 'mysql' );

var credfull = {
        email: "[email protected]",
        password: "secret"
    },
    crednopwd = {
        email: "[email protected]"
    },
    whereclause = {
        uid: "blabla"
    };

console.log( mysql.format( 'UPDATE user SET ? WHERE ?', [credfull, whereclause]) );
// UPDATE user SET `email` = '[email protected]', `password` = 'secret' WHERE `uid` = 'blabla'

console.log( mysql.format( 'UPDATE user SET ? WHERE ?', [crednopwd, whereclause]) );
// UPDATE user SET `email` = '[email protected]' WHERE `uid` = 'blabla'

Hope this helps.

answered Mar 29, 2015 at 7:21

tgo

1,5457 silver badges11 bronze badges

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

1 Comment

Husky Over a year ago

Thanks very much!! I read the part #Escaping query identifiers, no wonder no progress :P

Collectives™ on Stack Overflow

How to UPDATE a query with different arguments in MySQL

Environment

At first

My problem

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Environment

At first

My problem

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related