How can I update rows at random?

Question

I'd like to go through a table and randomly wipe out some of the data. I'm doing some data randomizing, turning real first names into fake ones etc. Well one of the tables involved has a column that is null about 40% of the time. My name randomizer app could do a coin toss somewhere in it when it assigns new first names. But I'd prefer to just do it at the end: randomly prune some of the data.

I have this code, which doesn't work, but sure looks like it should to me:

Use MyDb
go 

CREATE VIEW vRandNumber 
AS 
SELECT RAND() as RandNumber 

go  

CREATE FUNCTION RandNumber() 
RETURNS float 
AS 
  BEGIN 
  RETURN (SELECT RandNumber FROM vRandNumber) 
  END 

go  

select dbo.RandNumber()

update names set nickname = null 
where ((select dbo.RandNumber()) > 0.5)

When I run the RandNumber function it's fine, plenty random. But when I do the update it's updating all the rows half the time and none of the rows the other half of the time.

I want it to update a random number of rows every time I run the script. I really thought that a function like RandNumber would be run once for every row in the table. Apparently not.

Is this possible without a loop and without a console app?

Edit: I also tried it with a couple of variations of RAND() in the where directly and got the same results.

Answer 1

Assuming your Names table has a primary key field called Id, this will nullify the nickname in a random 50 percent of the rows:

update dbo.Names set Nickname = null where Id in
(
    select top 50 percent id from dbo.Names order by NEWID()
)