CODEWITHSUNDEEP
mysqlcomparesql-update
Mike L.
Mike L.

Reputation: 1966

Quick MySQL query question

I have two tables, both have the same columns. We will call them tilistings and tilistings_temp

Two columns in tilistings_temp, lat and lng are geocoded with Google API. tilistings has these same two columns, but they are empty. tilistings may contain some rows that are not in tilistings_temp, and vise versa.

Every night, tilistings is imported from a 3rd party I do not want to have to geocode the existing 2500 coordinates that are in tilistings_temp so my question is how do I compare the two tables and:

Set the values of lat and lng in tilistings to the corresponding values from tilistings_temp (both tables have mlsid in common)

Is it:

UPDATE tilistings
SET lat = tilistings_temp.lat, lng = tilistings_temp.lng
WHERE mlsid = tilistings_temp.mlsid;

??? Sorry for asking if this is right, I just can not risk corrupting the data inside the database.

Upvotes: 1

Views: 59

Answers (2)

a'r
a'r

Reputation: 37009

An update join might be quicker and its certainly less ugly than a double sub-query.

UPDATE tilistings a
    JOIN tilistings_temp b USING (mlsid)
SET a.lat = b.lat, a.lng = b.lng;

Upvotes: 1

Jé Queue
Jé Queue

Reputation: 10637

update tilistings a
set (a.lat,a.lng) = (
    select b.lat,b.lng
    from tilistings_temp b
    where b.mlsid = a.mlsid
);

EDIT: Just tried it and MySQL can't (yet?) understand compound attribute update like Oracle, so it gets a little uglier and likely slower when done via MySQL

update tilistings a
set a.lat = (
    select b.lat
    from tilistings_temp b
    where b.mlsid = a.mlsid
) , a.lng = (
    select c.lng
    from tilistings_temp c
    where c.mlsid = a.mlsid
);

Upvotes: 1

Related Questions