title: MySql INSERT...ON DUPLICATE UPDATE with partial VALUES
body: <p>I have a list of possibly-incomplete set of values that will be used to append to or update a MySql table using the INSERT...ON DUPLICATE KEY UPDATE construct. The requirements are as follows:</p>
  
  <ul>
  <li>If an operation resolves to an INSERT and the field value IS supplied, use the value supplied; </li>
  <li>If an operation resolves to an INSERT and the field value IS NOT supplied, use the field's table DEFAULT value;</li>
  <li>If an operation resolves to an UPDATE and the field value IS supplied, use the value supplied;</li>
  <li>If an operation resolves to an UPDATE and the field value IS NOT supplied, retain the current (table) field value.</li>
  </ul>
  
  <p>I've come up with the following statement, but the clauses wrapped in ** are erroneous and I'm having difficulty expressing them:</p>
  
  <pre><code>INSERT INTO `test` 
  (`id`, `num`, `text`)
  VALUES 
  ('1', 100, 'aaa'), 
  ('2', 200, DEFAULT),
  ('3', DEFAULT, 'ccc')
  ON DUPLICATE KEY UPDATE
  `num`  = IF (**VALUES(`num`)  = DEFAULT**, `num`,  VALUES(`num`)), 
  `text` = IF (**VALUES(`text`) = DEFAULT**, `text`, VALUES(`text`)); 
  </code></pre>
  
  <p>Notes: <code>id</code> is the unique key. Both <code>num</code> and <code>text</code> have default (NOT NULL) values set.</p>
  
  <p>Things I've tried, but aren't satisfactory:</p>
  
  <ol>
  <li><p>Replacing DEFAULT in VALUES with NULL, and then test for, e.g.,  <code>IF (VALUES (num) = NULL ...</code>. This works, but will insert NULL on INSERT (and generate a warning - e.g., "Column 'text' cannot be null"), which is not acceptable - I need to have the default value applied to the missing fields;</p></li>
  <li><p>Using something like 'xxx' instead of DEFAULT for missing values, and testing for 'xxx' (STRCMP), but this will insert 'xxx' in case of INSERT;</p></li>
  <li><p>I've not tried this as I can't find the command/proper syntax, but the idea is to test (in the IF clause) whether <code>num</code> and <code>text</code> in VALUES are literals (num or string) or a MySql keyword (i.e., DEFAULT) - possibly using regex? -  and then act accordingly.</p></li>
  </ol>
  
  <p>Of course, an alternative to the above might entail obtaining existing values from the database and/or hardcoding into the query the default values for the missing fields, but I trust the same result can be achieved more elegantly using a single MySql statement. </p>
  
  <p>Thanks in advance for your feedback.</p>