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

Edit your question and add the complete error message you are getting. Also add the schema of the table. — Sloan Thrasher, May 16 '17 at 11:19
Why isn't id an auto increment column? Is there a primary key? — Sloan Thrasher, May 16 '17 at 11:21
contact_table `id` is auto_increment and that field itself is the primary key. — Neethu George, May 16 '17 at 11:25
Then why are you trying to set it's value? The DB will do that automatically, and you shouldn't be trying to set it's value. — Sloan Thrasher, May 16 '17 at 11:27

score 1 · Answer 1 · answered May 16 '17 at 11:43

1

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

answered May 16 '17 at 11:43

andrew

9,313
7
30
61

I want to keep existing value (quite obvious). Suggested query does not work. Only if you put `club_id` in values with some dummy value, then it's ok, but it's so dirty hack – The Godfather Jul 08 '19 at 21:38
@TheGodfather did the first of the above 2 queries not work ? i would think that `club_id = club_id` should keep the existing value ? – andrew Jul 10 '19 at 03:58

score 0 · Answer 2 · answered May 16 '17 at 11:37

0

"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.

answered May 16 '17 at 11:37

Ram

221
1
2
7

I got this error while updating an existing entry. `club_id` is already there for id = '1234' – Neethu George May 16 '17 at 11:38
Please provide your table DDL structure and one row values you are trying to update. – Ram May 16 '17 at 11:42

score 0 · Answer 3 · answered Jul 08 '19 at 21:49

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 <...>

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

3 Answers3