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
0 comments:
Post a Comment