Reputation: 7691
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
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
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
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
Reputation: 11
UPDATE table1 SET name = 'HIGH' WHERE age IN (SELECT MAX(age) FROM table1 GROUP BY name)
Upvotes: -1
Reputation: 11
update table1 set Name='HIGH' where Age in(select max(Age) from table1)
Upvotes: -1
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
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
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
Reputation: 30855
try this
update table1 set name='HIGH' having age in(select max(age) from table1 group by type);
Upvotes: 0