July 30, 2003 at 7:19 am
Can someone please show me the syntax for cursor for update? I would like to use the cursor to update values in a table.
J. Moseley
[font="Courier New"]ZenDada[/font]
July 30, 2003 at 9:21 am
Don't use a cursor. Use a SET-based solution. Post the schema of the table and an explanation as to what you would like to update.
July 30, 2003 at 11:26 am
If you do want to use a Cursor for updating, you will need to ensure that the cursor is updatable. Some queries will return cursors where the cursor is not updatable.
If you have an updatable cursor, then you use it like this:
UPDATE MyTable
SET MyField = @MyValue
WHERE CURRENT OF c_MyCursor
July 30, 2003 at 11:33 am
Now, having said that, jpipes is mostly correct -- most things that are done with a Cursor can be done without one, and usually far more quickly. Contrary to what some folk will tell you, some things that can be done without a cursor are done more efficiently with a cursor, but this is a rare case.
July 31, 2003 at 8:27 am
Here is a simplified version of the task:
Table Lottery
AcctNo varchar(16)
Cycle varchar(2)
Bal numeric(9,2)
EmpNo varchar(6) - starts as null - want to assign empno's to this field randomly
Table Emp
EmpNo varchar(6)
Cycle varchar(2)
I need to assign empno's to accounts matching on cycles (think of cycles as teams). I need to make sure that employees get ~ the same number of accounts and average balances. On days where there are fewer accounts to assign than employees in that cycle, I still have to assign - so in that case the assignment still has to be random, except that no employee who 'wins the lottery' should get more than one account.
J. Moseley
[font="Courier New"]ZenDada[/font]
July 31, 2003 at 10:18 am
This ended up being my solution - not designed for speed for sure but it's not too slow given the size of my dataset - it gives me exactly what I want:
declare cur_empno cursor
dynamic
for SELECT EmpNo
FROM vw_Staff v
WHERE CYC = @CYC
ORDER BY NewID()
open cur_empno
fetch next from cur_empno into @EmpNo
declare cur_randass cursor
keyset
for SELECT EmpNo
FROM tbl_Placements
WHERE EmpNo IS NULL
AND (CYC = @CYC)
AND Bal < 11500.00
AND PlcmtDt = dbo.fxn_truncdate(getdate())
ORDER BY NewID()
open cur_randass
fetch next from cur_randass into @RandAss
while(@@fetch_status=0)begin
UPDATE tbl_Placements
SET EmpNo = @EmpNo
WHERE CURRENT OF cur_randass
fetch next from cur_empno into @EmpNo
if (@@fetch_status<>0)fetch first from cur_empno into @EmpNo
fetch next from cur_randass into @RandAss
J. Moseley
[font="Courier New"]ZenDada[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply