May 25, 2012 at 9:33 am
cphite (5/25/2012)
It would be possible to write something that could do that; but it's a bad idea. Whenever you're updating data - especially an entire column - it's better to keep it as simple and straightforward as possible. If you need to update multiple columns, then take the time to correctly update the columns.A more basic problem is that once you do this - replace column values with arbitrary numbers - you've basically lost any ability to relate this data to anything else. Are there any other tables in your database with SSN? If so, how are you going to match "1001" with it's corresponding SSN?
Why not just create a view that replaces the SSN with an ID number (using the row_number technique that you've been shown in this thread) and then have your users access the view rather than the table directly? That way you still have the benefit of a natural key (SSN) instead of an arbitrary counter?
+ 1. Totally agree.
May 25, 2012 at 9:36 am
Thanks everyone for the comments! And again I apologize for my lack of knowledge on the subject, I am very new to sql and these forums, thanks for being patient with me.
May 25, 2012 at 9:42 am
cphite (5/25/2012)
blampe (5/25/2012)
The countSSN procedure runs fine, but what I am trying to do is actually physically replace the values in the table with these count values generated by the countSSN procedure. In essence, I want to replace the entire ssn column with the column generated by the countSSN procedure.A few people have given you code to do this... the reason your stored procedure didn't do what you want is that nowhere in it do you actually UPDATE anything. It's just selecting columns.
I was also curious about command line parameters in SQL would it be possible to pass the column name as a parameter to run this procedure on different columns in the table?
It would be possible to write something that could do that; but it's a bad idea. Whenever you're updating data - especially an entire column - it's better to keep it as simple and straightforward as possible. If you need to update multiple columns, then take the time to correctly update the columns.
A more basic problem is that once you do this - replace column values with arbitrary numbers - you've basically lost any ability to relate this data to anything else. Are there any other tables in your database with SSN? If so, how are you going to match "1001" with it's corresponding SSN?
Why not just create a view that replaces the SSN with an ID number (using the row_number technique that you've been shown in this thread) and then have your users access the view rather than the table directly? That way you still have the benefit of a natural key (SSN) instead of an arbitrary counter?
I have to disagree. Please read the following snippet from the original post (emphasis is mine).
I want to preface this question by saying that I am new to SQL and new to these forums so bear with me if I am not being concise enough on the problem. I am trying to figure out how I can traverse through a specified column in a table and change the values in a table to an incrementing counter.
He didn't state the purpose, but it could be that this is for a dev and QA environment. Obfuscating the data.
May 25, 2012 at 11:48 am
Lynn Pettis (5/25/2012)
cphite (5/25/2012)
blampe (5/25/2012)
The countSSN procedure runs fine, but what I am trying to do is actually physically replace the values in the table with these count values generated by the countSSN procedure. In essence, I want to replace the entire ssn column with the column generated by the countSSN procedure.A few people have given you code to do this... the reason your stored procedure didn't do what you want is that nowhere in it do you actually UPDATE anything. It's just selecting columns.
I was also curious about command line parameters in SQL would it be possible to pass the column name as a parameter to run this procedure on different columns in the table?
It would be possible to write something that could do that; but it's a bad idea. Whenever you're updating data - especially an entire column - it's better to keep it as simple and straightforward as possible. If you need to update multiple columns, then take the time to correctly update the columns.
A more basic problem is that once you do this - replace column values with arbitrary numbers - you've basically lost any ability to relate this data to anything else. Are there any other tables in your database with SSN? If so, how are you going to match "1001" with it's corresponding SSN?
Why not just create a view that replaces the SSN with an ID number (using the row_number technique that you've been shown in this thread) and then have your users access the view rather than the table directly? That way you still have the benefit of a natural key (SSN) instead of an arbitrary counter?
I have to disagree. Please read the following snippet from the original post (emphasis is mine).
I want to preface this question by saying that I am new to SQL and new to these forums so bear with me if I am not being concise enough on the problem. I am trying to figure out how I can traverse through a specified column in a table and change the values in a table to an incrementing counter.
He didn't state the purpose, but it could be that this is for a dev and QA environment. Obfuscating the data.
I have to disagree with your disagreement 😉
Data integrity is just as important in a development or QA environment as it is in production. If he truly needs to obfuscate the data, he should do it in a way that ensures that the SSN is changed consistently across the entire database. You don't get that with a simple counter.
May 25, 2012 at 2:03 pm
cphite (5/25/2012)
Lynn Pettis (5/25/2012)
cphite (5/25/2012)
blampe (5/25/2012)
The countSSN procedure runs fine, but what I am trying to do is actually physically replace the values in the table with these count values generated by the countSSN procedure. In essence, I want to replace the entire ssn column with the column generated by the countSSN procedure.A few people have given you code to do this... the reason your stored procedure didn't do what you want is that nowhere in it do you actually UPDATE anything. It's just selecting columns.
I was also curious about command line parameters in SQL would it be possible to pass the column name as a parameter to run this procedure on different columns in the table?
It would be possible to write something that could do that; but it's a bad idea. Whenever you're updating data - especially an entire column - it's better to keep it as simple and straightforward as possible. If you need to update multiple columns, then take the time to correctly update the columns.
A more basic problem is that once you do this - replace column values with arbitrary numbers - you've basically lost any ability to relate this data to anything else. Are there any other tables in your database with SSN? If so, how are you going to match "1001" with it's corresponding SSN?
Why not just create a view that replaces the SSN with an ID number (using the row_number technique that you've been shown in this thread) and then have your users access the view rather than the table directly? That way you still have the benefit of a natural key (SSN) instead of an arbitrary counter?
I have to disagree. Please read the following snippet from the original post (emphasis is mine).
I want to preface this question by saying that I am new to SQL and new to these forums so bear with me if I am not being concise enough on the problem. I am trying to figure out how I can traverse through a specified column in a table and change the values in a table to an incrementing counter.
He didn't state the purpose, but it could be that this is for a dev and QA environment. Obfuscating the data.
I have to disagree with your disagreement 😉
Data integrity is just as important in a development or QA environment as it is in production. If he truly needs to obfuscate the data, he should do it in a way that ensures that the SSN is changed consistently across the entire database. You don't get that with a simple counter.
I still have to disagree. We don't have the full picture here. How do we know if the individuals ssn is stored anywhere else in the database? Personally, it shouldn't be. One, it is PII that should be kept secure in some manner. Two, it should not be used as a link to other tables.
If it is stored elsewhere, then the OP needs to expand what is provided to ensure that all other instances of ssn are properly updated. That is something that isn't difficult to accomplish if you have the database available and the knowledge to traverse it. And this all can start from the basic update I already provided.
May 25, 2012 at 2:06 pm
In addition, if you reread the original post, the modification of the ssn in the table was merely an example. Does this mean that this is actually what the OP is trying to accomplish? That is hard to say since we can't see from here what he sees there.
May 25, 2012 at 3:32 pm
I still have to disagree. We don't have the full picture here. How do we know if the individuals ssn is stored anywhere else in the database? Personally, it shouldn't be. One, it is PII that should be kept secure in some manner. Two, it should not be used as a link to other tables.
For me, the fact that we don't have the full picture here is all the more reason *not* to advise this person to update an entire column of data that is, more likely than not, a primary key. I tend to agree with you that SSN shouldn't be used as one, but if it is being used that way then he needs to understand that there is more involved here than just an update statement.
Hopefully the OP will provide more information before doing something he regrets :unsure:
May 26, 2012 at 12:57 pm
If the goal is to replace the real SSN with something based simply on a counter (row number) and there is no concern about matching the new SSNs across tables , the solutions given work just fine except that they don't look like SSNs for testing purposes. There could actually be constraints on the table that look for dashes in the right spots, for example.
Assuming that there are no other validations other than the one I listed above, the following code will generate things that look like SSNs (nnn-nn-nnnn) based on increasing row numbers and update the original table with those values. If you need some particular order to the data, change the ORDER BY in the OVER clause to reflect that sort order.
WITH
cteEnumerate AS
(
SELECT NewSSN = STUFF(
STUFF(
RIGHT('000000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10)),9)
,6,0,'-')
,3,0,'-'),
SSN
FROM dbo.person
)
UPDATE cteEnumerate
SET SSN = NewSSN
;
Results from update "person" table.
fname lname ssn
----- ----- -----------
John Jay 00-000-0001
John Doe 00-000-0002
Jane Doe 00-000-0003
James Smith 00-000-0004
Jane Smith 00-000-0005
(5 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply