January 28, 2008 at 9:14 am
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
January 28, 2008 at 9:51 am
what you are missing is an UPDATE FROM statement.
you can say UPDATE [sometable]
from [someothertable]
where sometable.id=someothertable.id
in your case, you know where the data is...you were able to select it as a GROUP BY.
in this case you'd end up using something like this:
[font="Courier New"]
UPDATE def
SET NetSalary = sumofsalary
FROM
(
SELECT id, SUM (salary) AS sumofsalary
FROM abc
GROUP BY id
) X --alias for the subquery as a table
WHERE def.id = x.id
[/font]
Lowell
January 28, 2008 at 10:52 am
THNX LOWELL...
THTS PERFECT
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply