开发者

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_t开发者_JAVA百科emp.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.


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;


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
);
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜