Question 21996 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL How to add corresponding values to another table?

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

Fruit_Name Fruit_ID
Apple 01
Banana 02
Pear 03
Grape 04

to table 2 ID part.

Fruit_Name Fruit_ID Grown In
Apple Farm A
Pear Farm B
Apple Farm B

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    @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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search