help with update.... - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

help with update....

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-28 : 11:12:46

Hi,
i just need to write a stored proc which sums the salary (from abc table) by id
and the total must be written to NetSalary(deftable)
here is the sample data....

create table abc (id varchar (10), salary decimal)

insert into abc values ('10001', 26150.98)
insert into abc values ('10002', 26150.98)
insert into abc values ('10001', 1124.00)
insert into abc values ('10001', 345.98)
insert into abc values ('10002', 6547.90)
insert into abc values ('10003', 6548.88)

create table def (id varchar (10), NetSalary decimal)

insert into def values('10001',null)
insert into def values('10002',null)
insert into def values('10003',null)

this is what i tried...
create proc sp_xyz
as
begin
select sum (salary) As sumofsalary from abc
group by id
end
--exec sp_xyz

now iam not able to understand how to update the total in the netsalary column of def table.......
i think i need to write an update for this but i am not able to figure it out how to write...
can anyone help me with the above probs plz???
thanks,
raaj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:18:44

UPDATE x
SET x.NetSalary = y.theSalary
FROM def AS x
INNER JOIN (SELECT ID, SUM(Salary) AS theSalary FROM abc GROUP BY ID) AS y ON y.ID = x.ID

E 12°55'05.25"
N 56°04'39.16"

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-28 : 11:41:19

hey
thanks peso...
tht ran perfectly....

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-28 : 12:42:27

Question asked in the correct way. Answer in 6 mins.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources