MySQL 使用 group by 子句的 SQL 更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6898935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Update query with group by clause
提问by Nageswaran
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'.
编写查询以将每种类型中最大年龄人员的姓名更新为“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;
回答by Svetlana
I have changed the script from Derek and it works for me now:
我已经更改了 Derek 的脚本，现在它对我有用：
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'
回答by Derek Kromm
You can't use group by directly in an update statement. It'll have to look more like this:
您不能直接在更新语句中使用 group by。它必须看起来更像这样：
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;
回答by Vincent Malgrat
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.
您的查询将不起作用，因为您无法直接在按查询分组的情况下比较聚合和列值。此外，您无法更新聚合。
回答by Pratik
try this
尝试这个
update table1 set name='HIGH' having age in(select max(age) from table1 group by type);
回答by user3339750
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'#
回答by Mike Robinson
Since I looked-up this response and found it a little bit confusing to read, I experimented to confirm that the following query doeswork, confirming Svetlana's highly-upvoted original post:
由于我查找了此回复并发现阅读起来有点混乱，因此我尝试确认以下查询确实有效，从而确认了 Svetlana 的高投票原始帖子：
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.
现在你知道了……我也是。
回答by raj
update table1 set Name='HIGH' where Age in(select max(Age) from table1)
回答by Muhammad Aftab Javed
UPDATE table1 SET name = 'HIGH' WHERE age IN (SELECT MAX(age) FROM table1 GROUP BY name)
回答by Vinay
You cannot use a GroupBy clause for an Update Statement. You will have to use a sub query during that time
您不能将 GroupBy 子句用于更新语句。在此期间您将不得不使用子查询
Update table1
Set name = 'High'
From table1
Join (Select max(age), type from Table1 group by Table1) t ON table1.age = t.age
