CODEWITHSUNDEEP
mysql
Ales
Ales

Reputation: 537

MySQL Update foreign key with values

I have two tables:

ANIMALS
---------
id
name
specie_name
id_specie

SCPECIE
---------
id
specie_name

In Animals table I have all values except id_specie. How can I update rows in animals to get ids from table Specie by comparing specie_name from both tables?

Upvotes: 0

Views: 67

Answers (2)

Dondi Michael Stroma
Dondi Michael Stroma

Reputation: 4800

UPDATE ANIMALS SET id_specie = (SELECT id FROM SPECIE WHERE specie_name = ANIMALS.specie_name)

Upvotes: 1

John Woo
John Woo

Reputation: 263723

Join the two tables. Try this:

UPDATE Animals a INNER JOIN SPECIE b
        on a.specie_name =  b.specie_name
SET    a.id_specie = b.id

FOR REFERENCE

Upvotes: 3

Related Questions