Update with subquery - error returns more that one row

[mo]

I have 2 tables described below. I am trying to take latitude and longitude from table B and update table A. I could just use table B but it is huge, 1Mil+ records and even though indexed, queries are slow.

 

Table A: (cityzip)

 

ID | zip_code |      city | state | latitude | longitude

------------------------------------------------------------

1  | 30281    | Atlanta | GA    |              |

...

------------------------------------------------------------

 

Table B: (ip_geolocation)

goecode_id | ip | country_code | country_name | region_name | city | postal_code | latitude | longitude

----------------------------------------------------------------------------------------------------------------------------------------------

0                | 555555555 | USA                | United States | GA                  | Atlanta | 30281 | 34.000 | -45.099

...

-----------------------------------------------------------------------------------------------------------------------------------------------

 

UPDATE  `cityzip` AS a
SET `latitude` = (SELECT DISTINCT `latitude` 
                  FROM `ip_geolocation`
                  WHERE UPPER(`city`)              = UPPER(a.`city`)
                  AND   UPPER(`region_name`) = UPPER(a.`state`))
WHERE a.`latitude` = '';

Error #1242 - Subquery returns more than 1 row

[fenway]

Use IN, not =.

[mo]

If I use in for both city and state, wouldn't that return random results?

[fenway]

Sorry, I'm very confused -- how does DISTINCT return more than one row here?

[mikosiko]

possible for the same Region_name - City maybe

example : http://www.zipcodedownload.com/~Resources/Images/USAZIP5Premium.gif

 

I will run this query to find the records

SELECT region_name, city, count(geocode_id)
   FROM `ip_geolocation`
  GROUP BY region_name, city
  HAVING count(geocode_id) > 1

[DavidAM]

DISTINCT just means you will not get any duplicate rows; it does not mean you will only get a single row. If you are getting more than one row in the subquery with a DISTINCT, then there are DIFFERENT latitude values for that City/State combination.

 

You might try doing a JOIN instead of a subquery; of course, the latitude/longitude that you get will be randomly selected from the multiple entries in the second table.

[fenway]

Of course... I simply assumed that's why distinct was there to begin with.  That's what I get for not reading.

[mo]

Thanks for all the feedback. I actually downloaded a free zip code list and don't want to pay for a list just to have the lat and lon. I will try a join and if this does not work, I will copy table 2 and remove unneeded columns.

[mo]

I got it working using the following. Although I have to go state by state to keep the query from timing out.

 

UPDATE  `cityzip` AS a
SET `latitude` = (SELECT DISTINCT `latitude` 
                            FROM `ip_geolocation`
                            WHERE UPPER(`city`)              = UPPER(a.`city`)
                            AND   UPPER(`region_name_short`) = UPPER(a.`state`) limit 1)
WHERE a.`state` = 'AK'
AND   a.`latitude` = '';

[fenway]

Now I'm really confused -- what are you trying to achieve here?