updating rows with cursor problem

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

SQL Server Development (2000)

updating rows with cursor problem

Author

Topic

sql2005s
Starting Member

4 Posts

Posted - 2005-12-18 : 14:57:29

Hello!

I apologise for unfinished previous post. I would like to update some column values in the table using CURSORS. I would like to iterate through all table's rows.

Code I used in my stored procedure:

DECLARE crs CURSOR
FOR SELECT PostalCode FROM Person.Address
FOR UPDATE OF PostalCode
OPEN crs

UPDATE Person.Address
SET PostalCode = @i
WHERE CURRENT OF crs

CLOSE crs
DEALLOCATE crs

I got this Message (error):

Msg 16931, Level 16, State 1, Procedure krneki, Line 12
There are no rows in the current fetch buffer.
The statement has been terminated.

My table does not have any primary keys or indexes! I am using SQL server 2005, but I think this is not the reason.
I would be very thakful for any advise.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-18 : 16:27:04

U should have a fetch statement in the begining (fetch first) & before looping (fetch next).

Also u have a whole set of syntax errors.
Please check the syntax of cursors.

Whatever u do can be done without using cursors. Cursors r inefficient an avoid as much.

Also tell us what u want to do, v would be able to give u a better way of doing it.

sql2005s
Starting Member

4 Posts

Posted - 2005-12-18 : 16:38:17

Thanx a lot!
Without cursors? How could I add for example a column (named Random)to a table and fill it with random values?
ALTER table ADD (columnName, INT) - adding empty column
Is there any way to iterate over all rows in a table and set the colums value (Random) with a random number (there is a function RAND() in sql)?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-19 : 00:42:46

You can make use of newid() function

Update yourTable set intCol=cast(cast(convert(varchar(36),newid()) as varbinary) as int)

Madhivanan

Failing to plan is Planning to fail

sql2005s
Starting Member

4 Posts

Posted - 2005-12-20 : 06:33:35

Thak you! It works...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-20 : 07:10:35

What are you going to do with those random numbers?

Madhivanan

Failing to plan is Planning to fail

sql2005s
Starting Member

4 Posts

Posted - 2005-12-20 : 15:08:09

I am working on some Data mining project... I have to mix up rows in the table randomly and sort them into n groups. For example, if n=3, I have to sort rows randomly in 3 groups. So i add a column named group to the table and fill it with random numbers between 1 and 3. If the value of the column group is 2 for a row that means that row (record) belong to the group 2...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources