CODEWITHSUNDEEP
mysql
user5920920
user5920920

Reputation: 68

MYSQL Joining two tables with one identical column

I have two tables: table 1 = university and table 2 = school

I added university_id into the table 2 and I need to connect the two tables.

If university_name from table 1 and name from table 2 are identical, get the id from table 1 and replace it onto table 2 university_id

I am very new to sql so if you could explain that would be great. I have also tried the following with no avail!

     select a.id,b.name from university as a
     inner join school as b on a.university_name = b.name
     UPDATE `school` SET `university_id` = a.id WHERE a.university_name = b.name

Upvotes: 4

Views: 75

Answers (2)

RafaelCaballero
RafaelCaballero

Reputation: 1613

Something like 

UPDATE school a 
JOIN university b ON a.university_name = b.name
SET a.university_id = b.id

should work

Upvotes: 1

tomtaylor
tomtaylor

Reputation: 56

I cannot run a test right now... Maybe it does give you a hint.

UPDATE `school` s SET `university_id` = (SELECT u.id FROM `university` u WHERE u.name=s.university_name)

You might need to JOIN the school-table within the SELECT statement.

Upvotes: 1

Related Questions