MySQL update field based on min value of another field when grouped by a third

Question

I have read several threads on selecting for min, grouping etc but can't seem to create an efficient query to solve this. Please forgive if duplicate.

I have a table like:

ID  Date    Value   Tag  
1   1/1/13  500 NULL  
2   1/1/13  10 NULL  
3   1/1/13  12 NULL  
4   1/2/13  99 NULL  
5   1/2/13  136 NULL  
6   1/2/13  17 NULL

Basically want an update query that populates the TAG field with a value of 1 when it has the lowest value grouped by date. In this example that would be IDs 2 and 6.

update table set tag = 1 (select min?)

Answer 1

You can do this by calculating the min() and using a join to do the filtering:

update t join
       (select date, min(value) as minvalue
        from t
        group by date
       ) tmin
       on t.date = tmin.date and t.value = tmin.minvalue
    set tag = 1;