First of all, the fact that two tables don't have id
s 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