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
– Marc B, Commented Oct 23, 2014 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
– amelia kosy, Commented Oct 23, 2014 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
– Marc B, Commented Oct 23, 2014 at 16:45

Air · Accepted Answer · 2014-10-23 16:47:06Z

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.

answered Oct 23, 2014 at 16:47

Air

8,6852 gold badges56 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related