MySQL update based on main table data with cross table subquery

Question

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?

Answer 1

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