MYSQL Joining two tables with one identical column

Question

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

Possible duplicate of [How to use JOIN in UPDATE query?](http://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query) — sgeddes, Feb 12 '16 at 23:56
This is smelling a lot like Friday homework to me (since the duplicate above is exactly the same) — PaulG, Feb 12 '16 at 23:58

score 1 · Answer 1 · answered Feb 13 '16 at 00:00

1

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.

answered Feb 13 '16 at 00:00

tomtaylor

56
2

I have also tried this and it also worked thank you! – user5920920 Feb 13 '16 at 00:11

score 1 · Accepted Answer · answered Feb 13 '16 at 00:01

1

Something like

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

should work

answered Feb 13 '16 at 00:01

RafaelCaballero

1,555
2
17
24

It does work thank you so much that looks way more simpler then what I was trying to do! – user5920920 Feb 13 '16 at 00:06

MYSQL Joining two tables with one identical column

2 Answers2