CODEWITHSUNDEEP
sqlsql-updatemax
Ads
Ads

Reputation: 6691

is it possible to get the MAX of a column and update it with a single query?

I have a scenario were i need to find the maximum value of the column and then to update a row by increment one with the maximum value found. Can it be done this way? 

update student SET stud_rank=MAX(stud_rank)+1 where stud_id=6

Upvotes: 1

Views: 89

Answers (4)

Hardik Vinzava
Hardik Vinzava

Reputation: 988

if you execute your above query than you will get below error in sqlserver :-

An aggregate may not appear in the set list of an UPDATE statement.

You have to change your query as :-

update student SET stud_rank=(select MAX(stud_rank)+1 from student) where stud_id=6

Upvotes: 0

Luis LL
Luis LL

Reputation: 2993

if you need the MAX value for stud_id = 6 you can do as this 

update student 
SET stud_rank=MAX(MaxRank)+1 
FROM student s 
INNER JOIN (
    SELECT MAX(stud_rank) AS MaxRank, stud_id
    FROM  students
) topRank ON s.stud_id = topRank.stud_id 
WHERE s.stud_id=6

Upvotes: 0

Devart
Devart

Reputation: 121902

Try this one -

UPDATE student
SET stud_rank = (SELECT MAX(stud_rank) + 1 FROM student)
WHERE stud_id = 6

Upvotes: 1

roman
roman

Reputation: 117337

update student set
    stud_rank = (select max(stud_rank) from student) + 1
where stud_id=6

Upvotes: 2

Related Questions