mysql-按克劳斯分组的SQL更新查询
Name type Age
-------------------------------
Vijay 1 23
Kumar 2 26
Anand 3 29
Raju 2 23
Babu 1 21
Muthu 3 27
--------------------------------------
编写查询以将每种类型的最大年龄人的名字更新为“ HIGH”。
还请告诉我,为什么以下查询不起作用
update table1 set name='HIGH' having age = max(age) group by type;
Nageswaran asked 2020-07-31T04:24:50Z
9个解决方案
77 votes
我已经更改了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'
Svetlana answered 2020-07-31T04:25:04Z
8 votes
您不能在更新语句中直接使用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;
Derek Kromm answered 2020-07-31T04:25:25Z
2 votes
您可以使用半联接:
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
您的查询无法正常工作,因为您无法直接按查询分组比较汇总和列值。 此外,您无法更新汇总。
Vincent Malgrat answered 2020-07-31T04:25:50Z
0 votes
试试这个
update table1 set name='HIGH' having age in(select max(age) from table1 group by type);
Pratik answered 2020-07-31T04:26:10Z
0 votes
您可以使用以下代码。
Update table1#
inner Join (Select max(age) as age, type from Table1 group by Table1) t ON table.age = t.age#
Set name = 'High'#
user3339750 answered 2020-07-31T04:26:31Z
0 votes
由于我查找了此响应并发现它有点令人困惑,因此我尝试确认以下查询确实有效,从而确认了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;
现在您知道...我也是如此。
Mike Robinson answered 2020-07-31T04:26:55Z
-2 votes
update table1 set Name='HIGH' where Age in(select max(Age) from table1)
raj answered 2020-07-31T04:27:11Z
-2 votes
UPDATE table1 SET name = 'HIGH' WHERE age IN (SELECT MAX(age) FROM table1 GROUP BY name)
Muhammad Aftab Javed answered 2020-07-31T04:27:26Z
-2 votes
您不能对更新语句使用Group By子句。 在此期间,您将不得不使用子查询
Update table1
Set name = 'High'
From table1
Join (Select max(age), type from Table1 group by Table1) t ON table1.age = t.age
Vinay answered 2020-07-31T04:27:46Z