How to update a field with random data?

Question

I've got a new varchar(10) field in a database with 1000+ records. I'd like to update the table so I can have random data in the field. I'm looking for a SQL solution.

I know I can use a cursor, but that seems inelegant.

MS-SQL 2000,BTW

score 5 · Accepted Answer · answered Sep 17 '08 at 14:41

5

update MyTable Set RandomFld =  CONVERT(varchar(10), NEWID())

answered Sep 17 '08 at 14:41

James Curran

101,701
37
181
258

With this addition UPDATE table SET field = CONVERT(varchar(10), LEFT(NEWID(), 10)) – Dan Williams Sep 17 '08 at 14:51
Sorry, don't have SQLServer running here, so I couldn't test it. – James Curran Sep 17 '08 at 14:58
This assigns the same randomm value to every entry. http://stackoverflow.com/questions/94906/how-do-i-return-random-numbers-as-a-column-in-sql-server-2005#94951 will randomize each row, and works in SQL2000 – Adam Oct 08 '08 at 21:59

score 1 · Answer 2 · answered Sep 17 '08 at 14:39

1

You might be able to adapt something like this to load a test dataset of values, depending on what you are looking for

answered Sep 17 '08 at 14:39

Mitchel Sellers

62,228
14
110
173

score 1 · Answer 3 · answered Sep 17 '08 at 14:39

1

Additionally, if you are just doing this for testing or one time use I would say that an elegant solution is not really necessary.

answered Sep 17 '08 at 14:39

Mitchel Sellers

62,228
14
110
173

score 1 · Answer 4 · answered Sep 17 '08 at 14:42

1

Why not use the first 10 characters of an md5 checksum of the current timestamp and a random number?

answered Sep 17 '08 at 14:42

score 1 · Answer 5 · answered Sep 17 '08 at 14:47

Something like (untested code):

UPDATE yourtable
SET yourfield= CHAR(32+ROUND(RAND()*95,0));

Obviously, concatenate more random characters if you want up to ten chars. It's possible that the query optimizer might set all fields to the same value; in that case, I would try

SET yourfield=LEFT(yourfield,0)+CHAR…

to trick the optimizer into recalculating each time the expression.

score 0 · Answer 6 · answered Sep 17 '08 at 14:39

0

If this is a one time thing just to get data into the system I really see no issue with using a cursor as much as I hate cursors they do have their place.

answered Sep 17 '08 at 14:39

Jeremy Coenen

1,085
1
12
19

score 0 · Answer 7 · answered Sep 17 '08 at 14:41

0

How about this:

UPDATE TBL SET Field = LEFT( CONVERT(varchar(255), @myid),10)

answered Sep 17 '08 at 14:41

Stephen Wrighton

36,783
6
67
86

score 0 · Answer 8 · answered Sep 17 '08 at 14:42

0

if you are in SQL Server you can use

CAST(RAND() as varchar(10))

EDIT: This will only work inside an iteration. As part of a multi-row insert it will use the same RAND() result for each row.

answered Sep 17 '08 at 14:42

theo

8,501
3
23
22

How to update a field with random data?

8 Answers8