optimisteve optimisteve - 2 months ago 8
SQL Question

Scramble data ONLY within a particular column, other columns unchanged

I want to UPDATE a table / records in a column. I would like to SHUFFLE / SCRAMBLE everything ONLY within a column. All other columns will remain the same.

Below is an example

Table name:

Scrambtable


Title Firstname Lastname Telephone other columns...
1 Mr Adam Smith 001
2 Mrs Angela 002
3 Mr Bill Mowny 003
4 Miss Dame 004
5 Mr Boya Mala


I am interested in transforming it as per below

Title Firstname Lastname Telephone other columns...
1 Mr Adam Mala 001
2 Mrs Angela 002
3 Mr Bill 003
4 Miss Dame Smith 004
5 Mr Boya Towny


I tried to use the script below..it seems to have only shuffle one 2 lastnames out of the 600 lastname in the lastname column.

update t1
set t1.lastname=b.lastname
from Scrambtable t1
cross apply
(select top 1 t2.lastname
from
Scrambtable t2
where
t1.lastname<>t2.lastname
order by newid()
)b

Answer

One strategy here is to use a CTE which adds a row number, assigned in a random order. Then join the CTE to itself on the random row number and assign the last name.

WITH CTE1 AS
(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROW
    FROM Scrambtable
),
CTE2 AS
(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROW
    FROM Scrambtable
)

UPDATE t1
SET t1.Lastname = t2.Lastname
FROM CTE1 t1
INNER JOIN CTE2 t2
    ON t1.ROW = t2.ROW