Sorry for the vague question. I'm trying to add ID from Table 1

to table 2 ID part.

I want to put the same Fruit_ID from Table 1 into Table 2. So that it looks like | Fruit_Name | Fruit_ID | Grown In | | -------- | -------- | -------- | | Apple | 01 | Farm A | | Pear | 03 | Farm B | | Apple | 01 | Farm B | 1

There are 35 rows in Table 1, and 300 rows in Table 2. How do I do it?

I tried using ALTER TABLE Table2 ADD FOREIGN KEY (Fruit_ID) REFERENCES Table1(Fruit_ID);

but it didn't work.

CodePudding user response：

Can't give you the exact command right now but I want to point out that your DB probably shouldn't be organized like that. Usually you'd want every fruit to have an id and then only use those IDs in other tables. So your second table isn't supposed to have the names of the fruits at all, but only their ids.

CodePudding user response：

@Dom Anna, this works.

UPDATE Table_2 
INNER JOIN Table_1
ON Table_2.Fruit_Name=Table_1.Fruit_Name
SET Table_2.Fruit_ID=Table_1.Fruit_ID;

It may need to add a primary key (detail_ID) for the Table 2.