SQL Insert with Inner Join trying to insert into wrong column

Question

I have an existing table of products, and I'm trying to insert new attributes from other tables into my main product table.

Here's my query:

INSERT INTO company_attributes (amp)
    SELECT company_attr_amperage.amp
        FROM company_attr_amperage
        INNER JOIN company_attributes
        ON company_attr_amperage.product_id = company_attributes.product_id;

The error that I get is: Field 'product_id' doesn't have a default value.

I'm not trying to insert into the product_id column, I'm trying to insert into the amp column (as specified on row 1 of the query)

The amp column exists on the company_attributes table, but right now, every value is NULL

Thanks

Answer 1

You may just want to update the value in existing rows. If so:

UPDATE company_attributes ca JOIN
       company_attr_amperage caa
       ON caa.product_id = ca.product_id
    SET ca.amp = caa.amp;

Answer 2

INSERT's job is to put new rows into a table, in your case company_attributes. Those new rows must be valid. Your table definition says that there's no default value for a column called product_id in that table. So, MySQL doesn't know what to put into that column when you don't provide it with your INSERT operation.

So, when you insert a row you must provide a value for that column.

There's no way to put a value into a column without creating a row for it, or UPDATEing an existing row.