SQL Update query with group by clause

user_588747 0
Viewed 109508
Name         type       Age
-------------------------------
Vijay          1        23
Kumar          2        26
Anand          3        29
Raju           2        23
Babu           1        21
Muthu          3        27
--------------------------------------

Write a query to update the name of maximum age person in each type into 'HIGH'.

And also please tell me, why the following query is not working

update table1 set name='HIGH' having age = max(age) group by type;
mysql sql
9 Answers

Since I looked-up this response and found it a little bit confusing to read, I experimented to confirm that the following query does work, confirming Svetlana's highly-upvoted original post:

update archives_forum f
inner join ( select forum_id, 
    min(earliest_post) as earliest, 
    max(earliest_post) as latest 
  from archives_topic 
    group by forum_id 
  ) t 
  on (t.forum_id = f.id)
set f.earliest_post = t.earliest, f.latest_post = t.latest;

Now you know ... and so do I.

user_48118730
Related