Help in Update script - 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 in Update script

Author

Topic

shakilhyd
Starting Member

16 Posts

Posted - 2008-07-15 : 09:40:49

SELECT empid,type,vol
from curben where type in ('cash','hsaf') and empid in ('2013','5227')

empid type vol
---------- ---- ------------------------
2013 CASH 686.000000000000
2013 HSAF 445.000000000000
5227 CASH .000000000000
5227 HSAF 1174.000000000000

Now I want update the volume type-HSAF with volume of CASH+HSAF

I need following output after updating. Remember that CASH-volume is zero of empid-5227
What will be the update script. Please help

empid type vol
---------- ---- ------------------------
2013 CASH 686.000000000000
2013 HSAF 1131.000000000000 --686+445=1131

5227 CASH .000000000000
5227 HSAF 1174.000000000000 --0+1174=1174

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 09:55:34

You should do Running Totals in your front end application

Select empid,type,(select sum(vol) from table where empid=t.empid and type<=t.type) as vol from table t1
where type in ('cash','hsaf') and empid in ('2013','5227')

Madhivanan

Failing to plan is Planning to fail

shakilhyd
Starting Member

16 Posts

Posted - 2008-07-15 : 10:35:11

Hi,
Its wrong. Actually I want to update field such as

Update Curben
set vol=HSAFvolume+CASHvolume
where cash's volume>0

Can you please help in update script.
Thanks.

quote:
Originally posted by madhivanan

You should do Running Totals in your front end application

Select empid,type,(select sum(vol) from table where empid=t.empid and type<=t.type) as vol from table t1
where type in ('cash','hsaf') and empid in ('2013','5227')

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 11:01:01

I advise you to do this in front end application. If there is no option

Update t1
set t1.vol=t2.vol
from table t1 inner join
(Select empid,type,(select sum(vol) from table where empid=t.empid and type<=t.type) as vol from table t
where type in ('cash','hsaf') and empid in ('2013','5227')
) as t2 on t1.empid=t2.empid and t1.type=t2.type

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 13:56:54

quote:
Originally posted by shakilhyd

SELECT empid,type,vol
from curben where type in ('cash','hsaf') and empid in ('2013','5227')

empid type vol
---------- ---- ------------------------
2013 CASH 686.000000000000
2013 HSAF 445.000000000000
5227 CASH .000000000000
5227 HSAF 1174.000000000000

Now I want update the volume type-HSAF with volume of CASH+HSAF

I need following output after updating. Remember that CASH-volume is zero of empid-5227
What will be the update script. Please help

empid type vol
---------- ---- ------------------------
2013 CASH 686.000000000000
2013 HSAF 1131.000000000000 --686+445=1131

5227 CASH .000000000000
5227 HSAF 1174.000000000000 --0+1174=1174

will you be having only 1 CASH & HSAF records per empid then you can use

UPDATE t
SET t.vol=t.vol + t1.vol
FROm YoutTable t
INNER JOIN YourTable t1
ON t1.empid =t.empid
AND t1.type='CASH'
WHERE t.type='HSAF'

if this is not your scenario please explain how you want the update to happen?
simply from all CASH records of empid or sum of all cash records that happen before current HSAF (you should have a date field for that)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources