title: MySQL DUPLICATE KEY UPDATE fails to update due to a NOT NULL field which is already set
body: <p>I have a MySQL DB which is using strict mode so I need to fill all NOT NULL values when I insert a row. The API Im creating is using just DUPLICATE KEY UPDATE functionality to do both inserts/updates.</p>
  
  <p>The client application complains if any NOT NULL attributes are inserted which is expected. </p>
  
  <p>Basic example (id is primary key and theare are two fields that are NOT NULL aaa and xxx)</p>
  
  <pre><code>INSERT INTO tablename (aaa, xxx, id ) VALUES ( "value", "value", 1)
  ON DUPLICATE KEY UPDATE aaa=VALUES(aaa), xxx=VALUES(xxx)
  </code></pre>
  
  <p>All good so far. Once it is inserted, the system would allow doing updates. Nevertheless, I get the following error when updating only one of the fields. </p>
  
  <pre><code>INSERT INTO tablename (aaa, id ) VALUES ( "newValue", 1)
  ON DUPLICATE KEY UPDATE aaa=VALUES(aaa)
  
  java.sql.SQLException: Field 'xxx' doesn't have a default value
  </code></pre>
  
  <p>This Exception is a lie as the row is already inserted and xxx attribute has "value" as value. I would expect the following sentence to be equivalent to:</p>
  
  <pre><code>UPDATE tablename SET aaa="newValue" WHERE id=1
  </code></pre>
  
  <p>I would be glad if someone can shed some light about this issue.</p>
  
  <p>Edit: </p>
  
  <p>I can use the SQL query in PhpMyAdmin successfully to update just one field so I am afraid that this is not a SQL problem but a driver problem with JDBC. That may not have solution then.</p>
  
  <p>@Marc B: Your insight is probably true and would indicate what I just described. That would mean that there is a bug in JDBC as it should not do that check when the insert is of ON DUPLICATE type as there may be a default value for the row after all. Can't provide real table data but I believe that all explained above is quite clear.</p>
  
  <p>@ruakh: It does not fail to insert, neither I am expecting delayed validation. One requirement I have is to have both insert/updates done using the same query as the servlet does not know if the row exists or not. The JAVA API service only fails to update a row that has NOT NULL fields which were already filled when the insert was done. The exception is a lie because the field DOES have a default value as it was inserted before the update.</p>