help needed with update

[raaj820]

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

[ck9663]

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

option 1:
if you will create def table from abc table, why not just use a SELECT...INTO... something like...

select id, sum(salary) as NetSalary into def from abc group by id

option 2:
if you have an existing def table and you need the update...try something like

UPDATE abc
set NetSalary = TotalSalary
from (select id, sum(salary) as TotalSalary into def from abc group by id) SummaryTable
where SummaryTable.id = abc.id
 

-- CK

[raaj820]

yep...i got it...
thnx for ur reply...