Solved

Using Cursor to update record

Posted on 2009-05-19
5
507 Views
Last Modified: 2012-05-07
I have a series of records that look like the sample below:
tblID  tkID         execQuantity   RemainingShares   QuantityFilled
-----  --------     ------------        ---------------           ---------------
1      71345691     400                100                          NULL
2      71345691     500                    0                            NULL
3      71345697     100                 400                          NULL
4      71345697     400                 100                          NULL
5      71345697     500                     0                          NULL
6      71345699     300                 200                          NULL
7      71345699     500                     0                         NULL
8      71345700     300                 700                         NULL
9      71345700     700                 300                        NULL
10     71345700     900                100                        NULL
11     71345700    1000                   0                        NULL
How can I use a cursor to update the quantityFilled column to look like this: for each group of tkID the first QuantityFilled = execQuantity (400 for tblID 1) and the second QuantityFilled = (execQuantity for tblID 2 minus execQuantity for tblID 1) 500 - 400 = 100 (this example referes to tkID 71345691
Than we do the same for tkID 71345697.
I need help to complete the code that is below
Declare @tblID Varchar(50)

Declare @tkID varchar(50)

Declare @execQuantity varchar(50)

Declare @RemainingShares varchar(50)

Declare @QuantityFilled varchar(50)
 
 

Declare GetQuantity CURSOR FOR
 

select  tblID,tkID,

execQuantity,RemainingShares,QuantityFilled

from OrderJournalGroupWorksheet
 
 

OPEN GetQuantity

FETCH NEXT FROM GetQuantity

INTO @tblID,@tkID,@execQuantity,@RemainingShares,@QuantityFilled
 

WHILE @@FETCH_STATUS = 0
 

BEGIN

Need help here	
 
 

     FETCH NEXT FROM GetQuantity

     INTO @tblID, @tkID,@execQuantity,@RemainingShares,@QuantityFilled

END
 

CLOSE GetQuantity

DEALLOCATE GetQuantity

Open in new window

0
Comment
Question by:bookspan
  • 3
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24422994
no reason for a cursor...

create table #t(tblID int , tkID int,         execQuantity int,   RemainingShares int,   QuantityFilled int)

insert into #t
select 1  ,    71345691  ,   400          ,      100,                          NULL union all
select 2  ,    71345691  ,   500         ,           0,                            NULL union all
select 3  ,    71345697  ,   100        ,         400  ,                        NULL union all
select 4  ,    71345697  ,   400       ,          100   ,                       NULL union all
select 5  ,    71345697  ,   500      ,               0 ,                         NULL union all
select 6  ,    71345699  ,   300     ,            200   ,                       NULL union all
select 7  ,    71345699  ,   500    ,                 0 ,                        NULL union all
select 8  ,    71345700  ,   300   ,              700   ,                      NULL union all
select 9  ,    71345700  ,   700  ,               300   ,                     NULL union all
select 10 ,    71345700  ,   900 ,               100    ,                    NULL union all
select 11 ,    71345700  ,  1000,                   0   ,                     NULL


update t
set QuantityFilled = calc
from #t t
join
(
select top 100 percent *,
calc = execquantity - (select SUM(execQuantity)
from #t i where i.tkID = o.tkid
and i.tblid < o.tblid
)
from #t o
order by tblID asc
) r on t.tblid = r.tblid
0
 
LVL 23

Expert Comment

by:adathelad
ID: 24423109
I originally came up with something very similar to chapmandew which  I think was wrong as it gave negative QuantityFilled values in the case of tkID 71345700. So, assuming the SUM is wrong and you do only want to substract the execQuantity from the PREVIOUS tblID *only* for a given tkID then try this:


CREATE TABLE #tmp_OrderJournalGroupWorksheet

(

tblID INTEGER,

tkID INTEGER,

execQuantity INTEGER,

RemainingShares INTEGER,

QuantityFilled INTEGER

)
 

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (1,71345691,400,100,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (2,71345691,500,0,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (3,71345697,100,400,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (4,71345697,400,100,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (5,71345697,500,0,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (6,71345699,300,200,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (7,71345699,500,0,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (8,71345700,300,700,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (9,71345700,700,300,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (10,71345700,900,100,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (11,71345700,1000,0,NULL)
 

UPDATE w1

SET w1.QuantityFilled = w1.execQuantity - ISNULL((SELECT TOP 1 w2.execQuantity FROM #tmp_OrderJournalGroupWorksheet w2 WHERE w2.tkID = w1.tkID AND w2.tblID < w1.tblID ORDER BY w2.tblID DESC), 0)

FROM #tmp_OrderJournalGroupWorksheet w1
 

SELECT * FROM #tmp_OrderJournalGroupWorksheet
 

DROP TABLE #tmp_OrderJournalGroupWorksheet

Open in new window

0
 
LVL 23

Expert Comment

by:adathelad
ID: 24423133
The results for this were:
tblID       tkID        execQuantity RemainingShares QuantityFilled
----------- ----------- ------------ --------------- --------------
1           71345691    400          100             400
2           71345691    500          0               100
3           71345697    100          400             100
4           71345697    400          100             300
5           71345697    500          0               100
6           71345699    300          200             300
7           71345699    500          0               200
8           71345700    300          700             300
9           71345700    700          300             400
10          71345700    900          100             200
11          71345700    1000         0               100
0
 

Author Comment

by:bookspan
ID: 24423344
Adathelad Solution works better.
What is the name of that type of update?
0
 
LVL 23

Accepted Solution

by:
adathelad earned 500 total points
ID: 24423387
There isn't a specific name for this type of UPDATE - it's just a set-based operation using a subquery to set the QuantityFilled column.

Basically you should always try to do things as set-based operations like this, instead of using cursors to update rows one at a time as it's much more performant.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now