MySQL default value error with ON DUPLICATE KEY UPDATE with sql mode ON

Question

Common query for both create and update contact. While doing an updation for a field:- An exception occurred while executing

INSERT INTO contact_table 
SET id = '1234',
is_stealth_mode = 1 
ON DUPLICATE KEY UPDATE 
id = LAST_INSERT_ID( id ),
is_stealth_mode = VALUES(is_stealth_mode)

General error: 1364 Field 'club_id' doesn't have a default value

contact_table has a field club_id which has default value none and not nullable

strict mode is enabled

contact_table already has value id = '1234' and 'club_id' = 123

Answer 1

"contact_table has a field club_id which has default value none and not nullable"

From your above statement, is it means that club_id doesn't have default value or it's default value is 'NONE'. It seems to be club_id is not nullable and you should provide that value in the query.

Answer 2

You need to specify what each column should update to in the ON DUPLICATE KEY UPDATE clause

id = LAST_INSERT_ID( id ),
is_stealth_mode = VALUES(is_stealth_mode),
club_id = club_id

to keep the existing value

Or

id = LAST_INSERT_ID( id ),
is_stealth_mode = VALUES(is_stealth_mode),
club_id = VALUES(club_id)

if you want to update the field value

Answer 3

The idea here is that just normal INSERT is being executed first, and since your DB has NOT NULL constraint, it complains. So ON DUPLICATE part doesn't really matter (see also https://stackoverflow.com/a/17479690/1657819).

One of possible options - put dummy value for club_id field, but not use it in UPDATE part.

But of course, if no such id exists, then the entry with dummy data will be created.

    INSERT INTO contact_table 
      SET id = '1234',
      is_stealth_mode = 1 ,
      club_id="dummy"  <---- Note dummy value which is not used in ON DUPLICATE part
    ON DUPLICATE KEY UPDATE 
      id = LAST_INSERT_ID( id ),
      is_stealth_mode = VALUES(is_stealth_mode)

As an alternative, you can change the schema to allow NULL values:

ALTER TABLE contact_table
    CHANGE COLUMN `club_id` `club_id` INT NULL <...>