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

RandNumber is a function. Functions in SQL must output the same result every time for the same input, unless underlying database data has changed. This is the mathematical definition of a function (as opposed to how a normal programming language treats a "function," which is more of a function-like construct).

Since the result of your function should never change during an update statement (which is an atomic operation) the query plan compiler for your query only calls RandNumber once and then caches the result.

You may be able to get away with just referencing RAND directly in your query, but if that still doesn't work, you'll have to do this iteratively in a stored procedure.

Answer 2

How about

update names set nickname = null
where  abs(checksum(nickname) % 2) = 0

Answer 3

try something like this:

WHERE DATEPART(ms,CreateDate)>500

where "CreateDate" is a column already in the table that has an actual date and time in it. Tthe millisecond should be fairly random

EDIT here's another approach:

DECLARE @YourTable table (RowID int, RowValue varchar(5))
INSERT INTO @YourTable VALUES (1,'one')
INSERT INTO @YourTable VALUES (2,'two')
INSERT INTO @YourTable VALUES (3,'three')

SELECT 
    RAND(row_number() over(order by RowID)+DATEPART(ms,GETDATE())),* 
    FROM @YourTable

OUTPUT run 1:

                       RowID       RowValue
---------------------- ----------- --------
0.716200609189072      1           one
0.71621924216033       2           two
0.716237875131588      3           three

(3 row(s) affected)

OUTPUT run 2:

                       RowID       RowValue
---------------------- ----------- --------
0.727007732518828      1           one
0.727026365490086      2           two
0.727044998461344      3           three

(3 row(s) affected)

Answer 4

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()
)

Answer 5

RAND() (and GetDate/CURRENT_TIMESTAMP) is evaluated once per statement. You need someway to get around that. One way is (if you have a convenient row valued integer, e.g. an ID column), is to call RAND(ID) instead.

Answer 6

RAND() persists within a query.

SELECT  RAND()
FROM    names

will give you a set of equal numbers.

You need to do something like this:

WITH    q AS
        (
        SELECT  *,
                ABS(CHECKSUM(NEWID())) % 2 AS r
        FROM    names
        )
UPDATE  q
SET     nickname = NULL
WHERE   r = 0