updating rows using cursor problem
-
Hello!
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.
-
Ziga,
The reason for the error is you have not FETCHed anything.
Shifting gears... I know you think you need a cursor for this, but you do not. I will admit a little confusion on your code... for example... I have no clue what "@i" is.
If you want to update one table from another, please post the names of the 2 tables and the relevant columns... one of us will save your SQL life by showing you how to do it without a cursor. Cursors are "death by SQL".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanx a lot for reply! My task is something like that: I have a table with some (for example 3 FirstName, LastName, Age - this is just the example) attributes without primary key. What I have to do is to add a column (ID) to the table, fill it with values from 1 to n (depends on number of rows in the table) and set this column as a primary key. So each column must be identified. I saw the solution as stored procedure with the following:
-adding a column (empty) to the table (ALTER TABLE myTable ADD(id, INT))
-iterate over all the rows in the table and UPDATE id column in each row using CURSOR (i dont know for any other solution for iterating over the rows...).
FETCH NEXT does similar as SELECT does, but I don t have any idea how to update the attribute ID in all rows...
The example I found on the internet was similar to this:
DECLARE @i
SET @i = 1
DECLARE crs CURSOR
FOR SELECT id FROM myTable
FOR UPDATE OF id //this clause make CURSOR updatable
WHILE FETCH_STATUS = 0 //here should cursor iterate over the rows in the table?
BEGIN
UPDATE myTable
SET id = @i
WHERE CURRENT OF crs //updating value depending on cursors current value
SET @i=@i+1
END
Another part of the task I have to do is similar. I have to add a column to the table and fill it with random values.
But the error occours, which I posted in my original thread.
Any idea how to solve this problem? Is it any way to solve it without cursors?
Another part of the task I have to do is similar. to the first one (adding key).I have to add another column to the table and fill it with random values...
Thanx a lot,
Ziga
-
What you may want to do is create a table myTable1 with identical structure as yours plus one additional field with IDENTITY attribute. Then simply insert into this table all records from your first myTable and then drop your original myTable and rename the myTable1 to myTable.
This is going to work if you don't have any foreign keys. If you do have foreign keys then just drop them before you drop myTable and then recreate them after you done.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Thanx, it is an idea! Althou it would work very slow, specially if a table has a lot of attributes and a lot of records...
-
I think you should be able to get it done in just few minutes.
The table create statement shouldn't take you more then a minute. (just use the script from the original table and add one field).
The insert statement should take you another minute.
Then, if your table is not extremally huge, you have few minutes for the coffee.
Renaming takes no time...(almost).
PS. Timewise it bits the cursor approach.
Both in coding and execution.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Easy enough...
First, let's take care of adding the ID column, filling it with numbers from 1 to n, and making it the Primary Key... we're going to do it without a cursor or loop of any kind. In fact, we're going to do it ALL in a single statement and it won't even be an update...
ALTER TABLE MyTable
ADD ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
Now, that wasn't too hard, eh? When you add an IDENTITY column to an existing table (one must NOT already exist), it will auto-magically populate from 1 to n. No need to create a separate table and copy and all that other stuff...
3 down, 2 to go. Next problem... let's add a column to hold your random numbers... we'll call it "RandomNum" just because it seems to make sense.... and, let's populate it to solve your last 2 problems all in a single step just like last time...
ALTER TABLE MyTable
ADD RandomNum INT NOT NULL
DEFAULT RAND(CAST(NEWID() AS VARBINARY(36)))*1000000.0
Look Ma! No cursors, no loops... Heck... we didn't even use an UPDATE.
In case anyone is wondering, the NEWID() function returns a unique 36 character string of hexidecimal numbers and is being used as a unique seed for the RAND function which, as most of you know, does not, by itself, work randomly in a single update like when a column is added. The times-a-million will give us random numbers from 0 to 1000000 because of the rounding that occurs when converting a decimal number to and INT.
By the way, Ziga... I hope you really are using SQL Server 2000 because half this stuff won't work in any other RDBMS. If this is for a class you're taking, then your Instructor is going to have a bird because I imagine he/she wanted you to do this the hard way with UPDATEs and all. If you're not actually using MS-SQL Server and just thought this forum might be a good place to get an answer, post back and we'll try to help. Same thing goes if your instructor passes a stone over the way I solved this and we'll fall back and do it all the hard way.
Since you're new, lemme just tell you that a cursor is the second worst thing you could ever do to code. They should probably be avoided at all cost. If your Instructor is actually trying to teach you how to use cursors
instead of how to avoid them, then lemme know where you're going to school because your instructor needs to be shot out of a cannon butt first. --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hey Jeff
I have similar requirement, so as to generate rows from 1 to n to an existing table but the new column to added shall not be with Identity property Any idea ?
shrikant
Regards
Shrikant Kulkarni -
Thank you Jeff for taking your time for my task. Your solution is very simple and useful! It is probbably night in you cuntry on the other side of the ocean. It is the middle of the day here in Europe
.Actually I am using SQL Server in the matter of fact SQL Server 2005. I am doing some Data mining stuff on the Faculty using SQL Server Data Mining tools. I have to implement some technique called cross validation in .NET and SQL Server 2005. Most of the tables i got do not have any primary key, so I have to add it. The second thing is that I have to mix up all the rows in the table randomly and sort them in n groups (n is the number you define, for example 3). I should do that, because the sequence of rows in the table could depend on some attribute. My idea for solving this problem was to add another column to the table. And fill that column with random numbers between 1 and 3 if the number of groups is 3 (n=3). It means that approximatly 33% of the rows will belong to group 1, 33% to group 2 and 33% to group 3. The important thing is that this distribution is random.
Firs I wanted to update a column with random numbers from my aplication, row by row. But now I see it is possible to do it directly on the server using:
ALTER TABLE MyTable
ADD RandomNum INT NOT NULL
DEFAULT RAND(CAST(NEWID() AS VARBINARY(36)))*1000000.0
If I multiply VARBINARY(36) with 2, it will probbably give me random numbers between 0 and 2. That is what I need.
Slow cursors were my idea
. I won't use them any more. But are they still the only way to iterate over all the rows in the table, aren't they?Thanx a lot for help!
Ziga
-
Ziga,
To do a nearly perfect distribution of random numbers across 3 sets, use the following to overcome the up-rounding that is inherenent in an INT column (you could use TinyInt as well)...
ALTER TABLE MyTable
ADD RandomNum INT NOT NULL
DEFAULT FLOOR(RAND(CAST(NEWID() AS VARBINARY(36)))*3.0)+1
The above will produce random numbers from 1 to 3 with a nice even distribution instead of biasing against the end number. Yes, this should all work on SQL Server 2005 but, if for some reason it doesn't, please write back.
Glad the cursor thing was the idea of one of the instructors or I'd have to fly the big pond to talk with them up close an personal.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Shrikant,
Yes, but I need a little more info...
1. Do the tables have a Primary Key?
2. How many rows does the biggest table have?
3. Are you allowed to use temporary tables?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Shrikant,
This will do it provided your tables correctly have a primary key (composite 2 column PK in this case)... first, the usual disclaimer because this is a data altering script that you must make changes to...
DO NOT EXPERIMENT ON LIVE DATA! MAKE A COPY OF THE TABLE AND EXPERIMENT ON IT, INSTEAD. MAKE BACKUPS OF YOUR TABLES BEFORE APPLYING ANY SOLUTION THAT MODIFIES TABLES OR DATA. I AM NOT NOR CAN I BE MADE TO BE RESPONSIBLE FOR YOUR OR ANYONE ELSES DATA!!!
--===== If the temporary numbering table exists, drop it
IF OBJECT_ID('TempDB..#OneToN') IS NOT NULL
DROP TABLE #OneToN
--===== Add the new but empty column to your table
ALTER TABLE YourTable
ADD SeqNum INT
--===== Create a temporary numbering table and populate
-- it with both the primary key info from your table
-- and the numbers from 1 to N
SELECT IDENTITY(INT,1,1) AS N,
<PK Column1 in YourTable> AS PK1,
<PK Column2 in YourTable> AS PK2
INTO #OneToN
FROM YourTable
--===== Update the new column in the original table with
-- the numbers we just created in the numbering table
-- using the primary key info to provide the match
UPDATE YourTable
SET SeqNum = t.N
FROM YourTable y,
#OneToN t
WHERE y.<PK Column1 in YourTable> = t.PK1
AND y.<PK Column2 in YourTable> = t.PK2
--===== Prepare the new column for a UNIQUE index
ALTER TABLE YourTable
ALTER COLUMN SeqNum INT NOT NULL
--===== Add a UNIQUE index to the new column to prevent dupes
CREATE UNIQUE NONCLUSTERED INDEX IX_YourTable_SeqNum
ON dbo.YourTable
(SeqNum)
--===== If the temporary numbering table exists, drop it
IF OBJECT_ID('TempDB..#OneToN') IS NOT NULL
DROP TABLE #OneToN
Of course, you need to make a couple of substitutions in the above:
Replace <PK Column1 in YourTable> and <PK Column2 in YourTable> with the names of the 2 columns that form a two column Primary Key in your original table. If you only have a single column PK, remove all rows that reference <PK Column2 in YourTable>. If the primary key has a 3rd or even a 4th column, you will need to add the appropriate rows for <PK Column3 in YourTable> and <PK Column4 in YourTable>.
SeqNum is my name for the numbered column. Feel free to replace that name with whatever you want.
Obviously, you will need to replace ALL instances the the word "YourTable" with the correct name of the table you want to add the numbered column to.
DO NOT EXPERIMENT ON LIVE DATA! MAKE A COPY OF THE TABLE AND EXPERIMENT ON IT, INSTEAD. MAKE BACKUPS OF YOUR TABLES BEFORE APPLYING ANY SOLUTION THAT MODIFIES TABLES OR DATA. I AM NOT NOR CAN I BE MADE TO BE RESPONSIBLE FOR YOUR OR ANYONE ELSES DATA!!!
I dont know why people don't just use an IDENTITY column for this. Keep in mind that any new rows you add to the altered table will be rejected if the SeqNum is NULL or is duplicated. If you need to maintain this column, DO NOT USE MAX+1 methods to add new SeqNums because you will eventually have records rejected just due to timing. You'll need to create and use a "Sequence Table". Start another thread, if that's your intent, and I'll show you the right way to use a Sequence Table so that it never experiences a Deadlock (the main problem with Sequence table usage).
If all you're trying to do is number an output in SQL Server 2000, then lot's of folks will show you a method using a triangular join sub-query. Be afraid, be very afraid... they bog down terribly after about 50k rows. A modification of the temp table method will do it much more nicely. In SQL Server 2005, you can use the new RowNum function for such an output with little or no fanfare.
If your DBA does not allow the use of temp tables to solve this immediate problem and won't allow you to create a working table in the database to do this, then your DBA is broken and you need to get a new one.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I tried to run
ALTER TABLE MyTable
ADD RandomNum INT NOT NULL
DEFAULT FLOOR(RAND(CAST(NEWID() AS VARBINARY(36)))*3.0)+1
on SQL Server 2005!
It works well!
Thanx,
Ziga
-
Outstanding, Ziga! Thank you for the feedback!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply