在sql中使用聚合函数更新语句

In an interview they asked me to write query "update the rows that has maximum percentage"...the column consists of studentName,maths,science,percentage...

在一次采访中，他们让我写查询“更新具有最大百分比的行”...该列包含studentName，数学，科学，百分比......

Please help me to write update query using aggregate function...

请帮我用聚合函数编写更新查询...

3 个解决方案

#1

update  YourTable
set     col1 = 42
where   percentage = 
        (
        select  max(percentage)
        from    YourTable
        )

#2

SQL FIDDLE DEMO

SQL FIDDLE DEMO

create table dummy (user_id int,name varchar(50),percentage int)
insert into dummy values(1,'Amit',99);
insert into dummy values(2,'Michle',80);
insert into dummy values(3,'Naval',60);
insert into dummy values(4,'Jack',56);

update dummy set percentage='100'
where percentage=(select max(percentage) from dummy)

#3

;WITH x AS 
(
  SELECT 
     studentName, maths, science, 
     r = RANK() OVER (ORDER BY percentage DESC)
  FROM dbo.table_name
)
UPDATE x 
  SET studentName = studentName + ' - Gold star'
  WHERE r = 1;