I have the following SELECT statement which I have simplified here. It actually takes 0.6s to run as it does some calculations based on two tables (table1 & table2).

SELECT 
    table1.id,table1.lat,table1.lon, 
    (SELECT id 
       FROM table2 
      WHERE latitude=table1.lat AND longitude=table1.lon 
      LIMIT 0,1) AS subqueryresult 
FROM 
    table1

Maybe this thread will even help me to improve my select. The two tables itself have no common ids.

What I intend to do is update every row of table1 with the value received in subqueryresult. subqueryresult itself results out of a SELECT based on the main table data (table1.lat & table1.lon) and just uses table2 as a reference.

Any idea how to achieve that?

First of all, the fact that two tables don't have ids in common does not mean you cannot join them. You can JOIN two tables using any boolean expression. This can be table1.id = table2.id, but in can also be table2.latitude = table1.lat AND table2.longitude = table1.latitude.

Let's assume these are your tables:

SELECT * FROM table1;
id |       lat |       lon
-: | --------: | --------:
 1 | 41.390205 |  2.154007
 2 | 40.416775 | -3.703790
 3 | 41.979401 |  2.821426
 4 | 40.304665 | -3.723679
SELECT * FROM table2;
 id |  latitude | longitude
--: | --------: | --------:
101 | 41.390205 |  2.154007
102 | 40.416775 | -3.703790
103 | 41.979401 |  2.821426
124 | 43.262985 | -2.935013

We could perform your original query with a subquery, and get:

SELECT 
    table1.id, 
    table1.lat, 
    table1.lon, 
    (SELECT id 
       FROM table2 
      WHERE latitude=table1.lat AND longitude=table1.lon 
      LIMIT 0,1
    ) AS subqueryresult 
FROM 
    table1
id |       lat |       lon | subqueryresult
-: | --------: | --------: | -------------:
 1 | 41.390205 |  2.154007 |            101
 2 | 40.416775 | -3.703790 |            102
 3 | 41.979401 |  2.821426 |            103
 4 | 40.304665 | -3.723679 |           null

(Nearly) the same can be done with a JOIN through the following query:

SELECT
    table1.id, table1.lat, table1.lon, table2.id AS table2_id
FROM
    table1
    JOIN table2 ON table2.latitude = table1.lat AND table2.longitude = table1.lon
id |       lat |       lon | table2_id
-: | --------: | --------: | --------:
 1 | 41.390205 |  2.154007 |       101
 2 | 40.416775 | -3.703790 |       102
 3 | 41.979401 |  2.821426 |       103

(If you want to get the row with no match on table2, you can use LEFT JOIN instead of JOIN).


If you want to UPDATE table1.id to be the same as table2.id, you can do so on MySQL by using:

UPDATE
    table1
    JOIN table2 ON table2.latitude = table1.lat AND table2.longitude = table1.lon
SET
    table1.id = table2.id

SELECT * FROM table1
 id |       lat |       lon
--: | --------: | --------:
  4 | 40.304665 | -3.723679
101 | 41.390205 |  2.154007
102 | 40.416775 | -3.703790
103 | 41.979401 |  2.821426

Note that this is not standard SQL, which does not allow to have a JOIN on this part of an UPDATE. If more than one matching row exists on table2, the value used for the update could be any of the matching ones.

The standard SQL way to do the same thing is:

UPDATE
    table1
SET
    id =         /* Your subquery! */ 
    (SELECT
        table2.id
    FROM
        table2 
    WHERE
            table2.latitude = table1.lat
        AND table2.longitude = table1.lon
    ORDER BY id     /* good to have a criterion to select id */
    LIMIT 1
    )
WHERE
    EXISTS     /* The subquery actually returns something */      
    (SELECT 
         table2.id
    FROM
        table2 
    WHERE
            table2.latitude = table1.lat
        AND table2.longitude = table1.lon
    ) ;

Or:

-- Standard SQL
UPDATE
    table1
SET
    id = coalesce(
        (SELECT
            table2.id
        FROM
            table2 
        WHERE
                table2.latitude = table1.lat
            AND table2.longitude = table1.lon
        ORDER BY id
        LIMIT 1
        ), table1.id
    ) ;

You can check everything at dbfiddle here

  • Thank you very much for the informative answer. The queries work as promised. However, if I run those on "large" table with both having more than 20000 entries it doesn't. Be reminded that my WHERE clause is much more complex. I don't really just compare those values, I calculate with them (I have to find the closest latitude/longitude from table2 based on the lat/lon from table1 for every row in table1) . I just didn't add it here for complexity reasons. I have changed your fiddle to what I tried so far. dbfiddle.uk/… – blackswan Jul 22 '17 at 19:56
  • You probably need to make some spatial indexes to find closer (lat, lng) points. See dev.mysql.com/doc/refman/5.7/en/using-spatial-indexes.html. I don't have much experience using them with MySQL. See also Fast nearest-location finder for SQL and latitude/longitude find nearest latitude/longitude - complex sql or complex calculation – joanolo Jul 23 '17 at 19:33
  • Finding the closest location is easy and even fast. Just combining it with the UPDATE doesn't work yet. I might have to use views to do it faster. – blackswan Jul 24 '17 at 6:06

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.