CODEWITHSUNDEEP
mysqlsql
Nageswaran
Nageswaran

Reputation: 7691

SQL Update query with group by clause

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;

Upvotes: 66

Views: 124958

Answers (9)

Svitlana
Svitlana

Reputation: 1776

I have changed the script from Derek and it works for me now (MySQL):

UPDATE table1 AS t 
INNER JOIN 
(SELECT type,max(age) mage FROM table1 GROUP BY type) t1 
ON t.type = t1.type AND t.age = t1.mage 
SET name='HIGH'

Upvotes: 116

Mike Robinson
Mike Robinson

Reputation: 8995

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.

Upvotes: 4

Vinay
Vinay

Reputation: 1064

You cannot use a GroupBy clause for an Update Statement. You will have to use a sub query during that time

Update table1
Set name = 'High'
From table1 
Join (Select max(age), type from Table1 group by Table1) t ON table1.age = t.age

Upvotes: -3

Muhammad Aftab Javed
Muhammad Aftab Javed

Reputation: 11

UPDATE table1 SET name = 'HIGH' WHERE age IN (SELECT MAX(age) FROM table1 GROUP BY name)

Upvotes: -1

raj
raj

Reputation: 11

update table1 set Name='HIGH' where Age in(select max(Age) from table1)

Upvotes: -1

user3339750
user3339750

Reputation: 1

you can use the below code.

Update table1#
inner Join (Select max(age) as age, type from Table1 group by Table1) t ON table.age = t.age#
Set name = 'High'#

Upvotes: 0

Derek
Derek

Reputation: 23318

You can't use group by directly in an update statement. It'll have to look more like this:

update t
set name='HIGH'
from table1 t
inner join (select type,max(age) mage from table1 group by type) t1
on t.type = t1.type and t.age = t1.mage;

Upvotes: 12

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

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.

Upvotes: 2

Pratik
Pratik

Reputation: 30855

try this

update table1 set name='HIGH' having age in(select max(age) from table1 group by type);

Upvotes: 0

Related Questions