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?
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.
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.
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?
