MySQL 更新内连接别名

Question

我正在尝试运行此查询：

UPDATE anothertable
INNER JOIN (SELECT *, 
                   LEAST(table1.from_price, table2.from_price, table3.from_price) AS cheapestPrice
              FROM (SELECT * FROM table1 v WHERE hotelid >= 1
                    UNION
                    SELECT * FROM table2 c WHERE hotelid >= 1
                    UNION
                    SELECT * FROM table3 k WHERE hotelid >= 1) AS temp      
           GROUP BY temp.hotelid, temp.country) AS i ON anothertable.id = i.hotelid 
                                                    AND anothertable.country = i.country
SET price = i.cheapestPrice, 
    op = i.to

但是我无法使用 LEAST 函数来访问名为“from_price”的字段。

有想法吗？

Answer 1

您应该使用 Min 而不是 Least：

Update anothertable
    Join    (
            Select hotelid, country, to
                , Min(from_price) AS cheapestPrice
            From    (
                    Select hotelid, country, from_price, to
                    From table1 v 
                    Where hotelid >= 1
                    Union
                    Select hotelid, country, from_price, to
                    From table2 c 
                    Where hotelid >= 1
                    Union
                    Select hotelid, country, from_price, to
                    From table3 k 
                    Where hotelid >= 1
                    ) AS temp
            Group By temp.hotelid, temp.country, temp.to
            ) As i 
        On anothertable.id = i.hotelid 
            And anothertable.country = i.country
Set price = i.cheapestPrice
    , op = i.to

编辑

正如注释中所指出的，我在内部临时查询中省略了 to 列。然而，我发现不清楚如何包含 to ，因为您在声明 Group By 列方面使用了 MySQL 的一个糟糕功能。我假设您需要在 Group By 中包含 to ，但是如果情况并非如此，您应该明确说明它应该在 to 上使用什么聚合函数柱子。

这是我在 to 列上使用 Min 的替代方案：

Update anothertable
    Join    (
            Select temp.hotelid, temp.country
                , Min(temp.to) As to
                , Min(temp.from_price) AS cheapestPrice
            From    (
                    Select v.hotelid, v.country, v.from_price, v.to
                    From table1 v 
                    Where hotelid >= 1
                    Union
                    Select c.hotelid, c.country, c.from_price, c.to
                    From table2 c 
                    Where hotelid >= 1
                    Union
                    Select k.hotelid, k.country, k.from_price, k.to
                    From table3 k 
                    Where hotelid >= 1
                    ) AS temp
            Group By temp.hotelid, temp.country
            ) As i 
        On anothertable.id = i.hotelid 
            And anothertable.country = i.country
Set price = i.cheapestPrice
    , op = i.to