mysql - 使用子查询更新非关系表

Question

我有以下 sql 查询，但当我执行时，它给我一条消息错误:“where 子句”中的未知列“t1.ip”。如果我在子查询中硬编码 t1.ip 只是为了测试，它会完美地工作。

UPDATE
    report_a t1,
    (SELECT
        location.country, region.name, location.city
    FROM
        geoip
    INNER JOIN
        location
    ON
        geoip.locId = location.locId
    INNER JOIN
        region
    ON
        region.country = location.country
    AND
        region.region = location.region
    WHERE
        INET_ATON(t1.ip) BETWEEN startIpNum AND endIpNum
    LIMIT 1) AS t2
    SET
        t1.country = t2.country,
        t1.city = t2.city,
        t1.state = t2.name;

有人知道我如何执行此查询吗？

谢谢

Answer 1

您正在寻找加入。我有点不清楚 LIMIT 1 应该做什么。我将其移至外部查询中，尽管这可能不正确:

UPDATE report_a t1,
       (SELECT location.country, region.name, location.city, startIpNum, endIpNum
        FROM geoip INNER JOIN
             location
             ON geoip.locId = location.locId INNER JOIN 
             region
             ON region.country = location.country AND
                region.region = location.region
       ) glr
       ON INET_ATON(t1.ip) BETWEEN glr.startIpNum AND glr.endIpNum
    SET t1.country = t2.country,
        t1.city = t2.city,
        t1.state = t2.name
    LIMIT 1;

据我所知，您可能不想要LIMIT 1。为什么子查询会有多个匹配项？