SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update totalprice base on itemprice and id		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kirank
Starting Member

USA
45 Posts
Posted - 12/22/2012 :  05:58:08  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 12/22/2012 :  06:29:37  Show Profile  Reply with Quote 

UPDATE t
SET totalprice = total
FROM (SELECT totalprice,SUM(itemprice) OVER (PARTITION BY id) AS total
      FROM table)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 12/22/2012 :  06:36:02  Show Profile  Reply with Quote
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
Starting Member

USA
45 Posts
Posted - 12/22/2012 :  07:31:11  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 12/22/2012 :  13:22:27  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000