SQL Insert with Inner Join trying to insert into wrong column

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

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;