mysql分段更新,mysql-按克劳斯分组的SQL更新查询

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