MySQL conditional insert causing Error Code 1364: "Field doesn't have a default value"

Question

I have added a new column to my table quote_entry (which already has several columns) with this statement:

ALTER TABLE quote_entry
ADD overtime_id INTEGER;

Now, I'm trying to insert data into the column like this:

INSERT INTO quote_entry (overtime_id)
select 1
FROM quote_entry 
WHERE overtime=0;

But this is giving me an error message:

Error: Field 'quote_id' doesn't have a default value
SQLState:  HY000
ErrorCode: 1364

I don't understand, why am I getting this error when I'm just trying to modify data in the overtime_id column?

you've got a `quote_id` field in your `quote_entry` table which DOESN'T have a default value, and since you're not providing a value for that field in your `insert...select` query, mysql is properly rejecting your query. — Marc B, Oct 23 '14 at 16:35
Yeah that's what I figured out. But I don't want to add new lines, just modify the ones already in the table. (i'm sorry if it's not clear, my English is not so rich) thanks for your quickly answer ! — amelia kosy, Oct 23 '14 at 16:44
then you need to have an `on duplicate key update ...` structure, or use an `update` query, not a plain insert. `insert` = create new record. — Marc B, Oct 23 '14 at 16:45

score 1 · Accepted Answer · answered Oct 23 '14 at 16:47

An INSERT statement is used to add new rows to a table. It looks like what you want to do is add values to the column you just created, in the existing rows. You would do this with an UPDATE statement instead of an INSERT statement:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

The simple version, without unnecessary options, would look like this:

UPDATE table_reference SET col_name=expr WHERE where_condition;

In this case, expr will be a constant expression – 1 – if you want to leave the column NULL in all rows where your condition doesn't match.

MySQL conditional insert causing Error Code 1364: "Field doesn't have a default value"

1 Answers1