How do I move the same columns data within the same table without looping

Question

Here is what I want to do:

I am trying to obfuscate data and create a working database to be used for documentation. I have a list of first names that I want to mix up in a table.

KEY FIRSTNAME  
135 CYNTHIA  
181 MARK  
186 MARGARET  
212 DESIVANITA  
506 AMY  
606 BRENDA   
628 KATHLEEN  
629 Johnna  
636 TAMARA   
652 KATHLEEN

This table has around 50K rows. I want to know if there is a way to mix up the names without using a loop?

Answer 1

I would suggest creating fake data instead of obfuscating real data. With fake data there's no possibility that you might expose sensitive information. Red Gate has a nifty tool to create test data, SQL Data Generator, that makes it trivial to generate realistic, fake data using various types of rules given your table schema.

Answer 2

One possibility is (assuming that you don't mind duplicate names):

DECLARE @T TABLE (ID INT, FirstNAme VARCHAR(10), newColumn VARCHAR(10))

INSERT INTO @T
        ( ID, FirstNAme )
SELECT 135 ,'CYNTHIA'
UNION ALL SELECT 181 ,'MARK'
UNION ALL SELECT 186 ,'MARGARET'
UNION ALL SELECT 212 ,'DESIVANITA'
UNION ALL SELECT 506 ,'AMY'

UPDATE @T
SET NewColumn = (SELECT TOP 1 FirstName FROM @T t2 ORDER BY NEWID())

SELECT *
FROM @t

Answer 3

I guess by using a cte and row_number() it can be done