MySQL: When updating, ignore empty strings

Question

I know how to do this using PHP, but I was wondering if MySQL provides for a simpler solution.

UPDATE users SET fname = 'badooka', lname = '' WHERE user_id='322';

If a value is empty (lname, in this particular case), how do I ignore it instead of overwriting the original with an empty string?

Answer 1

Do you mean UPDATE users SET fname = 'badooka', lname = '' WHERE user_id='322' AND lname != ''; Or do you mean have the database just not update that field that is empty? If the latter then no, there's not a simpler way, but the performance hit is negligible to update an additional field in a row that you are already updating.

Answer 2

Try this:

UPDATE users 
SET fname = IF(CHAR_LENGTH('badooka'),'badooka',fname), lname = IF(CHAR_LENGTH(''),'',lname) 
WHERE user_id='322';

That's the basic idea. If CHAR_LENGTH() returns 0, it'll use the original values.

Answer 3

The COALESCE operator will select the first non-null value of its arguments

so if you want to only update lname when lname is not empty, I might do something like this

UPDATE users SET fname = 'badooka', COALESCE( lname, SELECT lname FROM users where user_id='322' ) where user_id='322'

is that what you meant? this might be a bit of a performance hit if you're doing a lot, someone else know a better way?