MYSQL IF(expr1,expr2,expr3) but I don’t want any expr3 (if expr3 then don’t output anything.

Question

As the title says, I just want an output if the if is matched, if it’s not matched then I don’t want any output.

I currently have this, but it gives an error obviously

...rFormat=IF(ISNULL(rFormat), VALUES(rFormat),UNCHANGED)…

I looked around http://dev.mysql.com/doc/refman/5.4/en/control-flow-functions.html but didn’t really find out how to do it.

This question is kinda related to http://stackoverflow.com/questions/1837912/only-update-the-mysql-field-if-the-field-contains-null-or-0

This is used in context of: (as seen in the above URL)

………
ON DUPLICATE KEY UPDATE
rFormat=VALUES(rFormat),
rFiles=IF(ISNULL(rFiles), VALUES(rFiles), VALUES(rSizeMB)),
rText=VALUES(rText);

Answer 1

You could simply do

IF(ISNULL(rFormat), VALUES(rFormat), rFormat)

I'm not sure what you are trying to achieve by calling VALUES() at this point, however.

Answer 2

Just use WHERE rFormat IS NULL instead of IF.

Answer 3

Try this:

rFiles = COALESCE(existingColumnValue,'$newValue')

COALESCE returns the first non-null value. Note that you will need quotes around the $newValue if it is a string, and don't forget to escape. You won't use the quotes around the column name (existingColumnValue). If you are also looking for rows that have a (INT) 0 as the value as well as NULL, you might want to try this:

rFiles = CASE existingColumnValue WHEN NULL THEN '$newValue' WHEN 0 THEN '$newValue' ELSE existingColumnValue END CASE

I had to change your column names so that I could better express the solution. I hope that you still understand. I was assuming that the $newValue is not another column in the database, but a variable being passed from your script.