CODEWITHSUNDEEP
mysqlmysql-workbench
Fernanda Andretto
Fernanda Andretto

Reputation: 13

Error 1364 field doesn't have a default value when executing a simple insert with join

I got two simple tables, the field 'ID' is primary key on both. I want to insert the Table 2 field 'Calculated size' into the Table 1 field 'Size', by an inner join on 'ID'.

Table 1:

ID Size
1
2

Table 2:

ID Calculated_size
2 S

Expected result for Table 1:

ID Size
1
2 S

Here's my query:

insert into Table1 (Calculated_size)
select Size from Table2
inner join Table1
on Table2.ID = Table1.ID;

when executed, it gives error 1364: Field 'ID' doesn't have a default value. Why?

I tried turning off STRICT_TRANS_TABLES on SQL mode but it had no effect.

I'm using MySQL Workbench 8.0.15 and MySQL version 5.7.25-log

Thanks in advance

Upvotes: 0

Views: 2135

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You want update, not insert:

update table1 t1 join
       table2 t2
       on t1.id = t2iid
    set t1.Calculated_size = t2.size;

insert inserts new rows into the table -- the unmentioned columns have default values. update updates values in existing rows.

Upvotes: 1

Related Questions