update multiple columns BUT only if NULL

c_tcp_ip

Hi I'm trying to update a table with multiple values using one query. I'm able to update multilple columns however I want the columns to be updated ONLY if it's empty.

example pseudocode

UPDATE mytable SET value1='$value1', value2='$value2' WHERE phonenum='$phonenum' IF value1 !='' and value2 !=''.....

I've searched online but to no avail. is there a way to only have it update multiple fileds ONlY if nothing is in that field?

NogDog

. . . WHERE phonenum='$phonenum' AND value1 != '' AND value2 != ''

PS: Just for clarity, there is a difference between testing for an empty string versus null. If you actually want to check for null:

. . . WHERE phonenum='$phonenum' AND value1 IS NULL AND value2 IS NULL

Derokorian

Also for clarity:

WHERE phonenum='$phonenum' AND value1 != '' AND value2 != ''

Is saying where value1 is not empty, as opposed to checking if it is empty.

Edit: same statement for value2 😛

c_tcp_ip

thanks for the correction. So I actually want to do

UPDATE mytable SET value1='$value1', value2='$value2' WHERE phonenum='$phonenum' IF value1 ==' ' and value2 ==' '.....

any ideas as to a solution in mySQL?

NogDog

Those are just additional conditions for your WHERE clause, which you should separate with the AND operator, since you want them all to be true if the row is to be updated, as I provided in my previous reply -- just change the "!=" to "=" (SQL uses "=", not "==" as a comparison operator).

... WHERE phonenum='$phonenum' AND value1='' AND value2=''

If you want to separate the logic to process each field separately based on whether or not it is set (or NULL), you could use the IF() function:

UPDATE mytable SET
  value1 = IF(ISNULL(value1) OR value1='', '$value1', NULL), 
  value2 = IF(ISNULL(value2) OR value2='', '$value2', NULL)
WHERE phonenum='$phonenum'