I'm trying to anonymize a single column in a database through data shuffle.

I created this query but when I run it it update the column FirstName always with the same name:

BEGIN TRAN;

;WITH TCE_Shuffled
AS (
    SELECT ROW_NUMBER() OVER (
            ORDER BY [BusinessEntityID]
            ) AS ORIG_ROWNUM
        ,ROW_NUMBER() OVER (
            ORDER BY NEWID()
            ) AS NEW_ROWNUM
        ,*
    FROM [AdventureWorks2014].[Person].[Person]
    )
UPDATE [AdventureWorks2014].[Person].[Person]
SET FirstName = t2.FirstName
FROM TCE_Shuffled t1
JOIN TCE_Shuffled t2 ON t1.ORIG_ROWNUM = t2.NEW_ROWNUM

SELECT *
FROM [AdventureWorks2014].[Person].[Person]

ROLLBACK TRAN

SELECT *
FROM [AdventureWorks2014].[Person].[Person]

Here you can see that the column FirstName as the same value:

What am I doing wrong?

CodePudding user response：

This is the correct syntax for the UPDATE statement:

UPDATE t1
SET t1.FirstName = t2.FirstName
FROM TCE_Shuffled t1 JOIN TCE_Shuffled t2 
ON t1.ORIG_ROWNUM = t2.NEW_ROWNUM;