MySQL Update Inner Join Aliases

I'm trying to run this query:

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

However I 开发者_如何学编程cannot get the LEAST function to get access to a field called "from_price".

Ideas?

You should use Min instead of 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

Edit

As pointed out in comments, I omitted the to column from the inner temp query. However, it occurs to me that it isn't clear how to should be included because you are using an awful feature of MySQL with respect to declaring the Group By columns. I'm assuming that you need to include to in the Group By however if that is not the case, you should be explicit about what aggregate function it should use on the to column.

Here's an alternate where I use Min on the to column:

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