March 31, 2005 at 4:40 pm
Why is this script updating every record in the table? The table has 197 records in it but only 88 have a CRED_ID of NULL, therfore it should only update the 88 records.
DECLARE @Value INT, @CompanyName VARCHAR(50)
SET @Value = 1
DECLARE XYZ CURSOR
FOR
SELECT CompanyName
FROM WorkSite
WHERE CRED_ID IS NULL
OPEN XYZ
FETCH NEXT FROM XYZ INTO @CompanyName
WHILE (@@FETCH_STATUS) = 0
BEGIN
UPDATE WorkSite SET CRED_ID = @Value
FETCH NEXT FROM XYZ INTO @CompanyName
SET @Value = @Value + 1
END
CLOSE XYZ
DEALLOCATE XYZ
March 31, 2005 at 4:52 pm
You're updating the table instead of the cursor position!
WHILE (@@FETCH_STATUS) = 0
BEGIN
UPDATE WorkSite SET CRED_ID = @Value
Where Current Of XYZ <---- Add this
FETCH NEXT FROM XYZ INTO @CompanyName
SET @Value = @Value + 1
END
Edited to show Where Current OF
March 31, 2005 at 4:55 pm
where in update?
the code above update all (197) rows 197 times.
put current of or unique where in update statement.
March 31, 2005 at 5:13 pm
March 31, 2005 at 5:15 pm
March 31, 2005 at 5:29 pm
Please see my edited post above.
March 31, 2005 at 5:33 pm
Why are you using a cursor ?
Select Identity(int, 1, 1) As NewValue,
WorkSitePKEY
Into #Sequence
From WorkSite
Where Cred_ID IS NULL
Update WorkSite
Set Cred_ID = Cast( s.NewValue As varchar(10) )
From WorkSite As w
Inner Join #Sequence On s
Where s.WorkSitePKEY = w.WorkSitePKEY
[Edit] Just noticed the varchar(10) requirement and edited as required
March 31, 2005 at 5:33 pm
I'm sure with over 300 posts on this site you have heard of the CAST or CONVERT functions.
March 31, 2005 at 5:46 pm
March 31, 2005 at 5:53 pm
The set-based solution will scale better to a larger table. It will also translate easily to the new ROW_NUMBER ranking function coming in Sql Server 2005 that will get rid of the temp table part and make it 1 nice clean update out of a ranked derived table.
March 31, 2005 at 6:46 pm
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply