How can I update rows at random?
sql, sql-server-2008
Solution
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()
)
Problem
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.