CODEWITHSUNDEEP
sqlsql-server
frenchie
frenchie

Reputation: 51927

sql server: update table random

I have a table with 2 columns of sample data. Column1 is the primary key and is tied with several other tables. Column 2 is an int that randomly ranges from 1 to 5. I'm looking for a way to change the data in place in column 2 so that it ranges from 1 to 8. I'm not an expert in SQL and I was wondering what would be the easiest way to do this. There are about 3400 records so I guess I could go in manually and type numbers at random but I'm sure there's a better way to do it.

Any suggestions?

Thanks.

Upvotes: 2

Views: 953

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You can use RAND() function. Well, you could use it if it worked as someone would suppose.

This will NOT work in Sql-Server:

UPDATE tableT
  SET column2 = CAST(1 + (RAND() * 8) AS INT)
;

But this does:

UPDATE tableT
  SET column2 = 1 + 8 * RAND( CHECKSUM( NEWID() ) )
;

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453067

rand() is only evaluated once per query. So all your column2 values will be the same.

For SQL Server 2008 you can use

UPDATE tableT
  SET column2 = 1+ (CRYPT_GEN_RANDOM(1) % 8)

For earlier versions

UPDATE tableT
  SET column2 = 1+ (abs(checksum(NewId())) % 8)

Upvotes: 3

Aaron Kempf
Aaron Kempf

Reputation: 588

I think that your answer would actually return NINE, right?

I'd try this instead

UPDATE tableT SET column2 = CAST(1 + (RAND() * 7) AS INT);

Upvotes: 0

Related Questions