Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update totalprice base on itemprice and id

Author  Topic 

kirank
Yak Posting Veteran

58 Posts

Posted - 2012-12-22 : 05:58:08
Hi,

i need to update column result base on multiple records for ex,
i have 3 rows for id 3 , i need to update the totalprice coumns base on id. below itemprice is 10+20+30 so total price will be 60
how i can update the query for same.


id itemprice total price

2 10 60
2 20 60
2 30 60

thank you

---------------------------

http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-22 : 06:29:37
[code]
UPDATE t
SET totalprice = total
FROM (SELECT totalprice,SUM(itemprice) OVER (PARTITION BY id) AS total
FROM table)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-22 : 06:36:02
if below sql 2005

UPDATE t
SET totalprice=total
FROM table t
INNER JOIN (SELECT id,SUM(itemprice) AS total
FROM table
GROUP BY id)t1
On t1.id = t.id


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

kirank
Yak Posting Veteran

58 Posts

Posted - 2012-12-22 : 07:31:11
thanks a lot it works for me :)

---------------------------

http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-22 : 13:22:27
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -