Can't get my 1st cursor to change the data 2

[Big1934]

anyone who has used cursors should see the problem easily(I hope)
Its not erroring, just not affecting the columns,What obvious concept or code am I missing. yes, i have refreshed the table and still no changes

Declare crs_CoveragePremiums Cursor -- 2. Define the Cursor
For -- by associating
Select CP.CovID, -- it w/ a SELECT.
CP.Location,
CP.BaseRate,
A.Full_Address
From dbo.tblCoveragePremiums AS CP Left Join dbo.tblAddress AS A
On CP.CovID = A.CovID
where CP.Location Not Like '_u%'
Order By CP.PolicyBeginDate , A.Full_Address
For Update
Open crs_CoveragePremiums --3.Open the Cursor
Declare --4.Declaring 1 Vaiable per each column
@CovID int,
@Location varchar(30),
@BaseRate money,
@Full_Address varchar(max),
@extraVariable as money
print 'location name----------BaseRate------------'

FETCH NEXT FROM crs_CoveragePremiums INTO
@CovID, --these tell the cursor where to store the date in the selected row(s) of data
@Location,--must match number of columns,data type and size(which are defined above)
@BaseRate,
@Full_Address
--NEWTERM -KeySet term means the 'rows you are selecting' by the cursor
WHILE @@fetch_status = 0 BEGIN --loop will continue until the @@fetch_status = -1 or -2
If @BaseRate < 400
SET @BaseRate = @BaseRate * 1.25
print @Location PRINT @BaseRate
FETCH NEXT FROM crs_CoveragePremiums INTO
@CovID,
@Location,
@BaseRate,
@Full_Address
END
CLOSE crs_CoveragePremiums
DEALLOCATE crs_CoveragePremiums

 

[bborissov]

But you don't update anything
You just multiply variable by 1.25 and that's it. You didn't update table
BTW you don't need CURSOR for this. CURSORS can slow down performance. Use pure rowset based commands:

UPDATE tblCoveragePremiums SET BaseRate = BaseRate * 1.25
FROM dbo.tblCoveragePremiums tblCoveragePremiums
WHERE Location Not Like '_u%' AND
      BaseRate < 400

NOT TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

Boris is correct, using a cursor for this is a very bad idea. In fact if you ever find yourself using a cursor for any update, delete or insert, you probaly need to change your code to set-based code.

"NOTHING is more important in a database than integrity." ESquared