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?

Answer 1

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.