MySQL INSERT INTO Statement

Question

I need some help with an insert statement.

I've got:

my_table_a:

School        Latitude     Longitude
Old School     38.6...     -90.990...
New School     38.6...     -90.990...
Other School   38.6...     -90.990...
Main School    38.6...     -90.990...

my_table_b:

School        Latitude     Longitude
City School
Old School
Central School        
New School    
Other School   

I need to insert the latitudes and longitudes from my_table_a into my_table_b where there is a match between the school names. The problem is that table A doesn't have all of table B's schools and vice versa.

I've tried a WHERE clause but it's not working. I need to insert where my_table_a.school = my_table_b.school . Any suggestions?

Answer 1

Do you really want to insert or rather update?

What about

UPDATE my_table_b
set latitude = (select latitude from my_table_a where my_table_a.School = my_table_b.School),
    longitude = (select longitude from my_table_a where my_table_a.School = my_table_b.School)
where exists(select 1 from my_table_a where my_table_a.School = my_table_b.School)

This would be generic SQL. I am not sure if mysql supports an update of a join which would be a bit less repetitive and more efficient.

Answer 2

Using ANSI-92 syntax:

UPDATE TABLE_B
  JOIN TABLE_A ON TABLE_A.school = TABLE_B.school
  SET latitude = TABLE_A.latitude,
      longitude = TABLE_A.longitude

Using ANSI-89 syntax:

UPDATE TABLE_B, TABLE_A
  SET latitude = TABLE_A.latitude,
      longitude = TABLE_A.longitude
WHERE TABLE_A.school = TABLE_B.school