By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,481 Members | 1,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,481 IT Pros & Developers. It's quick & easy.

help needed with update

P: 5
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
Jan 28 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
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

Expand|Select|Wrap|Line Numbers
  1. UPDATE abc
  2. set NetSalary = TotalSalary
  3. from (select id, sum(salary) as TotalSalary into def from abc group by id) SummaryTable
  4. where SummaryTable.id = abc.id
  5.  

-- CK
Jan 28 '08 #2

P: 5
yep...i got it...
thnx for ur reply...
Jan 28 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.