Alex Rivera | Logout

SQL Update query with group by clause

Asked 2011-08-01T13:16:03.863
67
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
Edit
Report

1 Answer

2

You can use a semi-join:

SQL> UPDATE table1 t_outer
  2     SET NAME = 'HIGH'
  3   WHERE age >= ALL (SELECT age
  4                       FROM table1 t_inner
  5                      WHERE t_inner.type = t_outer.type);

3 rows updated

SQL> select * from table1;

NAME             TYPE AGE
---------- ---------- ----------
HIGH                1 23
HIGH                2 26
HIGH                3 29
Raju                2 23
Babu                1 21
Muthu               3 27

6 rows selected

Your query won't work because you can't compare an aggregate and a column value directly in a group by query. Furthermore you can't update an aggregate.

answered 2011-08-01T13:30:07.190

Your Answer