Update a column if the update query's value is not empty

All we need is an easy explanation of the problem, so here it is.

I am using Node.js and im trying to update my database. The SQL query looks like this

UPDATE user SET username = ?, email = ?, contact = ?, password = ?, type = ?, profile_pic = ? WHERE userid = ?
 dbConn.query(editUserQuery, [user.username, user.email, user.contact, user.password, user.type, img_name, userID], (error, results) => {
userid username email contact password type profile_pic
1 user example 1234 123 Admin 1
2 user2 example2 2345 234 Customer 1

However, the user may not fill in all the details so some of the values will be empty. Is there a way to only update a column if the ‘?’ value is not empty

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Depends on the value provided to MySQL when the parameter is not set.

If this value is NULL then simply not SET column = ? but SET column = COALESCE(?, column). So when NULL is provided then COALESCE returns existing value, and it is reassigned into (not changed), when the value other than NULL is provided then COALESCE returns it, and it is assigned to the column.

If this value is not NULL but some predefined literal (for example, empty string '') then use SET column = COALESCE(NULLIF(?, ''), column). Additional NULLIF converts this predefined value to NULL (and not changed any other value) then the expression acts like described above.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply