NodeJS MySQL Update If Null

Question

I'm using MySQL with NodeJS and I'm just pretty new.

I want to UPDATE col value if it's null else UPDATE for another col.

Simply like that: If col1 is null UPDATE else UPDATE col2.

    app.post('/api/update', (req, res) => {
        const convidn = req.body.conversationid
        const currentuser = req.body.current
     
    
       var sql = "UPDATE messages SET whodeleted='"+currentuser+"' WHERE converid = '" + convidn + "'";

That overwrites column value. I just want to if "whodeleted" is not null UPDATE for "whodeleted2" colum.

        db.query(sql, (err, result) => {
            if (err) throw err;
            console.log("Number of records deleted: " + result.affectedRows);
        });
    })

score 1 · Answer 1 · answered Sep 10 '21 at 21:51

1

You can do something like this

But your code is vulnerable to sql inject please read Preventing SQL injection in Node.js to so how you can avoid it

UPDATE messages 
SET 
    whodeleted = CASE
        WHEN whodeleted IS NULL THEN ?
        ELSE whodeleted
    END,
    col2 = CASE
        WHEN whodeleted IS NULL THEN col2
        ELSE 'test'
    END
WHERE
    converid = ?

answered Sep 10 '21 at 21:51

nbk

45,398
8
30
47

Thanks a lot for your support. But I have a trouble with multiline SQL query for NodeJS. How can i handle with that? – Sep 11 '21 at 19:51
see https://stackoverflow.com/questions/23266854/node-mysql-multiple-statements-in-one-query and please accept the answer – nbk Sep 11 '21 at 19:56

NodeJS MySQL Update If Null

1 Answers1