Update statement will not update data beyond 7 million plus rows out of 38 millions rows. HELP!!!

Question

Update statement will not update data beyond 7 million plus rows out of 38 millions rows. HELP!!!

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
December 12, 2014 at 10:17 am

#313071

Hi All,
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. This is driving me crazy as I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. :w00t: The 'Acct_Num_CH' field is an encrypted field (fyi). PLEASE HELP!
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

If I'm reading that correctly, won't it just start updating rows that already have the value 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v' ?

Try

UPDATE [dbo].[CC_Info_T]

SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'

WHERE [Acct_Num_CH] <> 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'

NB: Rowcount is deprecated, TOP is preferred:

WHILE EXISTS (SELECT * FROM [dbo].[CC_Info_T] WHERE [Acct_Num_CH] <> 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v')

BEGIN

UPDATE TOP 10000 [dbo].[CC_Info_T]

SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'

WHERE [Acct_Num_CH] <> 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'

END

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 2

davidsalazar01 (12/12/2014)
Hi All,
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. This is driving me crazy as I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. :w00t: The 'Acct_Num_CH' field is an encrypted field (fyi). PLEASE HELP!
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

Are you REALLY, REALLY sure that you want to change [font="Arial Black"]ALL [/font]non-null values???

--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)

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 3

Gazareth, I will try the update sample you sent.

Jeff, I definitely want to update all values (I don't think there are null values) as I need to sanitized all the data in the column with a new encrypted string which will act as "dummy data".

Thanks guys!

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

davidsalazar01 (12/12/2014)
Gazareth, I will try the update sample you sent.
Jeff, I definitely want to update all values (I don't think there are null values) as I need to sanitized all the data in the column with a new encrypted string which will act as "dummy data".
Thanks guys!

No worries, just be sure you understand the difference. I'd recommend checking that there are definitely no null values in the column and/or that not updating null values is consistent with your business' logic.

Jeff & I are getting at the same point - as the code's updating all non null values in a loop, it will increasingly begin updating columns that are already ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v to the same value.

Cheers

Gaz

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 5

Gazareth (12/12/2014)
davidsalazar01 (12/12/2014)
Gazareth, I will try the update sample you sent.
Jeff, I definitely want to update all values (I don't think there are null values) as I need to sanitized all the data in the column with a new encrypted string which will act as "dummy data".
Thanks guys!
No worries, just be sure you understand the difference. I'd recommend checking that there are definitely no null values in the column and/or that not updating null values is consistent with your business' logic.
Jeff & I are getting at the same point - as the code's updating all non null values in a loop, it will increasingly begin updating columns that are already ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v to the same value.
Cheers
Gaz

To wit, the code should be...

SET rowcount 10000

UPDATE [dbo].[CC_Info_T]

SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'

WHERE [Acct_Num_CH] <> 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'

WHILE @@ROWCOUNT = 10000

BEGIN

SET rowcount 10000

UPDATE [dbo].[CC_Info_T]

SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'

WHERE [Acct_Num_CH] <> 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'

END

SET rowcount 0

You'll also notice that I changed the value in the WHILE statement. The only set that won't be equal to 10000 is the last set. If you use >0, it'll do one full scan of the entire table and do no updates.

I also recommend that you use TOP rather than SET ROWCOUNT.

--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)