CODEWITHSUNDEEP
mysqlsqlphpmyadmin
user2250813
user2250813

Reputation: 11

SQL help - Updating a field in one table to match a field in another table based on matching a different field

Appreciate any help. I'm working on a db that was designed a bit poorly. I need to fix some bad data by updating one table based on another as follows (I am loosely describing the SQL I need):

Two tables:

I want to update the (currently incorrect) events table lat and lon to the (correct) airports table lat and lon.

Basically: UPDATE events WHERE <events.airport_id = airports.airport_id> SET events.lat = airports.lat

Then I can run it again for the lon.

I know I'm close, but not there with the exact syntax.

Thanks!

Upvotes: 1

Views: 249

Answers (2)

Younes
Younes

Reputation: 3293

This should also work:

UPDATE events e
set    e.lat = (select a.lat from airports a where a.airport_id = e.airport_id),
       e.lon = (select a.lon from airports a where a.airport_id = e.airport_id)
;

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Yes, you are close, but you need to UPDATE with JOIN. Here is right syntax for that:

UPDATE events AS e
INNER JOIN Airports AS a ON e.airport_id = a.airport_id
SET e.lat = a.lat,
    e.lon = a.lon;

You put the table references, the updated table which is specified in the UPDATE clause with the JOINed tables directly after the UPDATE clause then after that the SET clause, you can also include an optional WHERE clause at the end.

Upvotes: 0

Related Questions