Using IF() With ON DUPLICATE KEY UPDATE in MySQL

Question

I have a PHP script which runs a MySQL query.

$query = "INSERT INTO table (col1, col2)
          VALUES('$val1', '$val2')
          ON DUPLICATE KEY UPDATE col2= IF(IS NOT NULL '$val1', 'test', 'col2)";

Here is what I am trying to do: Col1 is the primary key. If there is a duplicate, it checks to see if the insert value for col2 is null. If not, it will update with the value, otherwise the value will stay the same.

This insert fails. When I try to run it manually in sqlyog (inserting actual values in place of variables), I get the following error: Error Code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IS NOT NULL.....'

I have checked the MySQL reference manual for the IS NOT NULL comparison operator (http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_is-not-null), and also for the INSERT ... ON DUPLICATE KEY UPDATE Syntax (http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) and believe I am using both correctly, but obviously that is not the case.

What am I doing wrong?

For reference, I am using MySQL 5 and the script is running on a RHEL5 server.

Answer 1

The syntax IS NOT NULL column is wrong. Correct: column IS NOT NULL. I'm not sure what this 'test' is about. You say that you want to either update the column or keep it as it is.

$query = "INSERT INTO table (col1, col2)
          VALUES('$val1', '$val2')
          ON DUPLICATE KEY UPDATE col2 = IF('$val2' IS NOT NULL, '$val2', col2)";

which can also be written as:

$query = "INSERT INTO table (col1, col2)
          VALUES('$val1', '$val2')
          ON DUPLICATE KEY UPDATE 
            col2 = COALESCE( VALUES(col2), col2)";