cultivate passion for everything else that goes on around programming

Monthly Archives: July 2010

Scramble Data in SQL Table


Given a request to randomly inter-change the values of some columns in a SQL Server table, I used the following SQL script:

   1: update Table1 set Column1 = (
   2:     select top 1 Column1 FROM Table1 t1
   3:     where Table1.Id != t1.Id
   4:     order by NEWID()
   5: )

We update the table Table1 using random values from the same table. The random row is selected by using this T-SQL:

   1: select top 1 Column1 FROM Table1
   2: order by NEWID()

The where clause is used to assure that the update takes a new random row for each row that will be updated.