UPDATE Statement with Aggregate function in WHERE condition

I seen many places developer get confused about how to use AGGREGATE function in WHERE condition with UPDATE statement. Suppose I have one table with user name and percentage. I want to update percentage if one user’s percentage sum is greater or lower than some value.

--table used

create table test11

(

uName varchar(20),

percentage int

)

--data used

INSERT INTO test11

SELECT 'RITESH',90 UNION ALL

SELECT 'RAJAN',75 UNION ALL

SELECT 'RITESH',80 UNION ALL

SELECT 'ALKA',70

--update query

update test11 set percentage=100

where uname='Ritesh'

AND

percentage in

(

select percentage from test11

WHERE uname='Ritesh' group by percentage having sum(percentage)>70

)

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com