Update only one column in user info - MySQL NodeJS

Question

I'm builing a classic CRUD (create, read, update, delete) API with NodeJS/Express and MySQL. I created a route to update my user informations that works fine.

The problem : If I dont send EVERY data (first_name, last_name etc...), the columns with no data update to undefined in MySQL database, and the column with data don't update. I would like that if I don't send data, no change happens for the columns, and only the one with datas change.

Here is my controller :

module.exports.updateUser = (req, res, next) => {
    if (req.method == "PUT") {
        let userDetails = `UPDATE users SET first_name = '${req.body.first_name}', last_name = '${req.body.last_name}', user_name = '${req.body.user_name}' WHERE id = ${req.params.id}`;

        sql.query(userDetails, function (err, result) {
            if (!err) {
                res.status(200).json({ message: "User infos updated." })
            } else {
                res.status(401).json({ message: "Error when updating user infos." })
            }
        })
    }
}

So, if I make a PUT request on an existing user in db with only the mail for example :

{
   "mail": "[email protected]"
}

all my user datas become null and user.mail stays the same.

Anyone could help me on this ?

Thank you 🙂

Answer 1

Use this query for update one or more filled update at a time

module.exports.updateUser = (req, res, next) => {
        if (req.method == "PUT") {
            let query = '';
            if(req.body.first_name){
               query = `first_name = 
                '${req.body.first_name}'`
            }else
            if(req.body.last_name){
               query = ` last_name = 
                '${req.body.last_name}'`
            }else
            if(req.body.user_name){
               query = `user_name = 
               '${req.body.user_name}'`
            }eles if(req.body.first_name 
                    && req.body.last_name) 
                  {
                    query =`first_name = '${req.body.first_name}',  last_name =    '${req.body.last_name}'`
                  }else if( 
                     req.body.last_name &&  req.body.user_name 
                  {
                    query = `last_name =  '${req.body.last_name}', user_name =   '${req.body.user_name}'`
                  }else if(req.body.first_name
                    && req.body.last_name && req.body.user_name) 
                  {
                    query =`first_name = '${req.body.first_name}', last_name =   '${req.body.last_name, user_name =       '${req.body.user_name}'}'`
                  }
            let userDetails = `UPDATE users SET ${query} WHERE id = ${req.params.id}`;
    
            sql.query(userDetails, function (err, result) {
                if (!err) {
                    res.status(200).json({ message: "User infos updated." })
                } else {
                    res.status(401).json({ message: "Error when updating user infos." })
                }
            })
        }
    }