SQL How to connect two tables together by a specific column?

Question

I have two tables: university and university_list

Table1 - university

Table2 - university_list

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

Thank you in advance!

Answer 1

select a.id,b.name from table1 as a
inner join table2 as b
on a.university_name = b.name

Above query will return id and name of university if match. Hold both value in variable and pass variable in update query.

update table2 set university_id = '$val' where b.name = '$name';

Answer 2

It is a simple join update You can update table 2 using below query

update ul
set university_id = u.id
from 
  university u inner join university_list ul on ul.name = u.university_name 

you also can refer to Join Update

Answer 3

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