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?

Do you mean you need to update my_table_b with the values from my_table_a? Or do you want to insert or update as appropriate? — Justin K
– Justin K, Commented Jul 30, 2010 at 21:11
Inserting creates new rows - to import data to matching schools, you need to use an UPDATE statement... — OMG Ponies
– OMG Ponies, Commented Jul 30, 2010 at 21:15
Hi, The latitude and longitude columns in my_tabke_b are empty. I can drop those 2 columns if it's easier. Basically, I need to get the latitude and longitude from my_table_a for schools that are also in my_table_b. So, I need the data for New School, Old School, and Other School. City School and Central School in my_table_b should be null or have 0.00. Thanks so much for your replies! — Laxmidi
– Laxmidi, Commented Jul 30, 2010 at 21:22

OMG Ponies · Accepted Answer · 2010-07-30 21:13:53Z

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

answered Jul 30, 2010 at 21:13

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank · Accepted Answer · 2010-07-30 21:13:28Z

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.

answered Jul 30, 2010 at 21:13

Frank

2,64816 silver badges14 bronze badges

3 Comments

OMG Ponies Over a year ago

+1: Not all databases support JOINs in the UPDATE statement - this is the most portable version.

Laxmidi Over a year ago

Hi Frank, Thanks so much. It worked perfectly. I was struggling with this problem for a couple of hours and you solved it in about 5 min. I learned something new about MySQL from you. Thanks.

Laxmidi Over a year ago

Hi OMG Ponies, Thank you for your message. As soon as I see your screen name pop up, I know that my problem will be solved soon. I think that you've posted on all of my mySQL problems-- and answered most of them. Thanks, again.

Collectives™ on Stack Overflow

MySQL INSERT INTO Statement

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related