mysql - update 语句中having 子句的用法

Question

我想使用此查询更新某些值。我只想使用所有出现过一次的城市名称的转储来更新这些行。请提出一个工作方法。

UPDATE city_mst c,
          iata_cities ic
LEFT JOIN airport_dump ad
ON        ad.city = c.city_name
AND       ad.iata = c.iata_code
AND       ad.iata IS NOT NULL
JOIN      country_mst cm
ON        c.country_id = cm.country_id
AND       IF(cm.country_name = "United States of America","United States",IF(cm.country_name = "Russian Federation","Russia",cm.country_name)) = ad.country
SET       c.iata_code = ad.iata,
          ic.iata_code = ad.iata,
          c.airport_lat = ad.latitude,
          c.airport_long = ad.longitude,
          ic.airport_lat = ad.latitude ,
          ic.airport_long = ad.longitude
group BY  c.city_name
HAVING    count(c.city_name) < 2 

我得到了 SQL 语法错误，我显然会得到这个错误。

相同的选择查询是:

-------------->> 城市 iata = 转储 iata 和 city_mst 中的单个条目

SELECT cm.country_name,
       Count(c.city_name),
       ad.country,
       c.city_id,
       ad.`airport id`,
       c.city_name  AS "CITY_MST | CITY_NAME",
       ad.city      AS "AIRPORT_DUMP | CITY_NAME",
       c.iata_code  AS "CITY_MST | IATA",
       ad.iata      AS "AIRPORT_DUMP | IATA",
       c.latitude   AS "CITY_MST | Latitude",
       ad.latitude  AS "AIRPORT_DUMP | Latitude",
       c.longitude  AS "CITY_MST | Longitude",
       ad.longitude AS "AIRPORT_DUMP | Longitude"
FROM   city_mst c
       LEFT JOIN airport_dump ad
              ON ad.city = c.city_name
                 AND ad.iata IS NOT NULL
                 AND ad.iata = c.iata_code
       JOIN country_mst cm
         ON c.country_id = cm.country_id
            AND IF(cm.country_name =
                    "united states of america", "united states", IF(
                    cm.country_name = "russian federation", "russia",
                country_name)) =
                ad.country
GROUP  BY c.city_name
HAVING Count(c.city_name) < 2

Answer 1

使用子查询:

UPDATE city_mst c LEFT JOIN
       (< your select query here>
       ) x
       ON c.city_id = x.city_id
    SET . . .;

在这些情况下，您的查询实际上应该按 city_id(而不是 city_name)聚合，并仅选择 SET 子句所需的列.

此外，INNER JOIN 可能没问题，但您还没有解释您想要做什么。