MySQL insert into table selecting data from another table

Question

I have a table Product that has a manufacturer field. The manufacturer field is currently of type VARCHAR.

I thought it better to create a table Manufacturer and alter the Product table to include a manufacturer_id field which of course shall be a foreign key referencing Manufacturer.id

So to migrate the data I want to insert into the new Product.manufacturer_id field the id of the Manufacturer whose name corresponds to the current Product.manufacturer field.

I'm having problems migrating the data using the following query:

INSERT INTO Product (manufacturer_id) 
   SELECT m.id FROM Manufacturer AS m WHERE m.name =
   SELECT p.manufacturer FROM Product AS p WHERE p.manufacturer = m.name;

Can anyone point me to where I'm going wrong?

An answer below looks like what you're after. But if you're still struggling, see: Why should I provide an MCVE for what seems to me to be a very simple SQL query? — Strawberry
– Strawberry, Commented Jun 22, 2017 at 10:47

Gordon Linoff · Accepted Answer · 2017-06-23 02:57:57Z

4

You want update, not insert:

update p
    set manufacturer_id = m.id
    from product p join
         manufacturer m
         on p.manufacturer = m.name;

The above is SQL Server syntax (not sure why I did that; the question is clearly tagged MySQL). The correct MySQL syntax is:

update product p join
       manufacturer m
       on p.manufacturer = m.name
    set p.manufacturer_id = m.id;

edited Jun 23, 2017 at 2:57

answered Jun 22, 2017 at 10:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user1061799 Over a year ago

Thanks for your reply and for getting me on the right track. I couldn't get the above query to work but the following sorted things: UPDATE Product p INNER JOIN Manufacturer m on p.manufacturer = m.name SET p.manufacturer_id = m.id;

Collectives™ on Stack Overflow

MySQL insert into table selecting data from another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related