CODEWITHSUNDEEP
mysql
Johan Fourie
Johan Fourie

Reputation: 179

MYSQL Update Join Statement

Update: I made a mistake in the import. All my "Building" fields in companies had an extra question mark. Not sure how this happened. Anyway this is solved.

A newbie question, (Yes, I have tried for hours and hours to do this)

I am trying to update a database with two tables.

The tables look similar to this.

latlon3:

  id building roadno block  lat2   lon2
   1  1331     5943    3    35.55  54.55

Companies:

  id building roadno block latitude longitude
   1  1331     5843    3     empty    empty

Ok. So I need to update the companies table, from the latlon3 table, if all three conditions are true(This is a must). The Building, RoadNo, and Block. This is what I have so far:

 UPDATE companies t
 JOIN latlon3 a USING (Building, RoadNo, Block)
 SET t.latitude = a.lat2, t.longitude = a.lon2
 WHERE t.latitude = '' AND t.longitude = ''

It always shows zero rows affected. This is my problem. The table companies don't update with the corresponding latitude and longitude fields from latlon3 table.

Any help would be appreciated.

Upvotes: 1

Views: 65

Answers (2)

Lucas Henrique
Lucas Henrique

Reputation: 1364

The roadno value is different in the two tables. How is using USING in this field, there is no relationship between tuples, and so returns 0 rows affected. I added the id in check

Try:

UPDATE companies t
 JOIN latlon3 a ON a.id=t.id SET t.latitude = a.lat2, t.longitude = a.lon2
 WHERE t.latitude = '' AND t.longitude = ''

Upvotes: 1

ravikumar
ravikumar

Reputation: 893

try this,

 UPDATE companies t
 JOIN latlon3 a ON a.building=t.building AND a.roadno=t.roadno AND a.block=t.block  SET t.latitude = a.lat2, t.longitude = a.lon2
 WHERE t.latitude = '' AND t.longitude = ''

Upvotes: 1

Related Questions