Solved

Using Cursor to update record

Posted on 2009-05-19
5
515 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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