Modifying column values for a large db table 1

[skurpyun]

Ok here's the situation that i need to resolve urgently. I have a table with 5 columns. One of those columns has data in it that should be encrypted that was erroneously left unencrypted. I need to go through 3000 rows and for each row, encrypt the value in that column. With me so far?

So essentially, i need to select that column, modify the value returned, and update the same column with the new value.

My question is how do i accomplish this? Being a web-focused programmer i immediately think of writing an ASP (or PHP) script that pulls the info out, modify it, then update the column. But, now i want to know if it's better (more efficient, faster, etc) to use a stored procedure, use PERL, or use any other method to accomplish what i wish to do, and how. Your input is greatly appreciated.

- sk

 

[Spellman]

It's probably better you do this as a stored procedure but it would probably depend on what method of encryption your using. If you do use a stored procedure you'll need to look at using cursors as well so it might be something a little like this:

CREATE PROC proc_name
AS

DECLARE @COLUMN1 VARCHAR(40)
DECLARE @COLUMN2 VARCHAR(40)
DECLARE @COLUMN3 VARCHAR(40)
DECLARE @COLUMN4 VARCHAR(40)
DECLARE @COLUMN5 VARCHAR(40)


DECLARE cCursor CURSOR
FOR SELECT Column1, column2, column3, column4, column 5 FROM tablename

OPEN cCursor

FETCH ccursor INTO @COLUMN1, @COLUMN2, @COLUMN3, @COLUMN4, @COLUMN5




WHILE @@SQLSTATUS = 0
BEGIN

Encryption Method

FETCH ccursor INTO @COLUMN1, @COLUMN2, @COLUMN3, @COLUMN4, @COLUMN5


END

CLOSE cCursor
DEALLOCATE CURSOR cCursor

It's a little bit tacky but hopefully you get the idea

Mark

The key to immortality is to make a big impression in this life!!

 

[skurpyun]

Actually, for the moment we've decided not to encrypt the value that exists in that column (lets call is Column ENC). Instead, for the current data, we will fill it with X's. In other words, I select column ENC from tablename and get valueA as a result. Instead of encrypting it, we will simply replace valueA with "xxxxxx" and update the db with that value.

In the future we will encrypt the data coming in from future inserts. But since the current data isn't gravely important (we still want to keep some trace of it by replacing each lett er with an 'x' however), we don't mind just "x-ing" it out.

Does that make any sense?

thanks for the quick response by the way Spellman. I'm gonna try out your method.

-sk

 

[JamesLean]

You should avoid cursors wherever possible! A simple update statement will solve your problem here:

UPDATE tbl
SET enc_col = REPLICATE('X', LEN(enc_col))

That will update all values to a string of Xs the same length as the original data.

--James

 

[skurpyun]

JamesLean and Spellman, thank you both for your help. I appreciate it. i tried both methods, but JamesLean's idea was the quickest and easiest. So i tried that and, aha, success.

I tried using the stored procedure, but could not get it to work quickly and it took a lot of playing around with, but i get the concept and its something I could call in the future for similar issues. As time was the issue, i just went with the update script JamesLean mentioned. But what bugs me is, JamesLean, why would you say not to use cursors whenever possible??

This is the code i used in query analyzer (of ms sql 2000):

update TABLENAME set COLUMN1 = Replicate('X', Len(COLUMN1)-4)+Substring(COLUMN1,Len(COLUMN1)-3,4)

As you can see i decided to get a bit more funky with it since the script was so easy and quick. Instead of completely replacing the values in COLUMN1 with all "X's", I decided to replace the digits with all X's except the last 4 digits. Thereby updating each row in COLUMN1 to something like "XXX1234". Easy enough....i just could not get it myself.
THANKS.
- sk