May 14, 2009 at 3:34 pm
RBarryYoung (5/14/2009)
Michael Valentine Jones (5/14/2009)
This will update your SSN column with 9 digit SSN numbers.
update MyTable
set
ssn = right(convert(bigint,convert(varbinary(7),newid())),9)
Michael: because you are truncating the ID to 9 digits, I do not think that you are gauranteed that they will be unique. And if they are random (and not in some kind of subsequence), then they are almost always going to generate duplicates (I think, I haven't done the math yet).
I didn't see the requirement for unique before.
This code example will take care of it, as long as you don't have a unique constraint on the column. Just loop to update the ones with duplicates until they are all gone. This ran in 55 seconds on my non-indexed temp table with 5 million rows.
print 'Update 1'
update #t
set
ssn = right(convert(bigint,convert(varbinary(7),newid())),9)
declare @rowcount int
declare @update_count int
set @rowcount = -1
set @update_count = 1
while @rowcount 0
begin
set @update_count = @update_count +1
print 'Update '+convert(varchar(20),@update_count)
update #t
set
ssn = right(convert(bigint,convert(varbinary(7),newid())),9)
where
ssn IN (select SSN from #t group by SSN having count(*) 1 )
select @rowcount = @@rowcount
end -- end while
Results:
Update 1
(5000000 row(s) affected)
Update 2
(25067 row(s) affected)
Update 3
(260 row(s) affected)
Update 4
(4 row(s) affected)
Update 5
(0 row(s) affected)
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply