I want to populate a column of my database table with 253 lines of 'M' and 'F' randomly placed in the column, is this possible?
Example of what it may look like:
Gender:
M
M
F
M
F
F
M
F
M
-
Which RDBMS are you targeting?vhadalgi– vhadalgi2017-07-05 12:13:16 +00:00Commented Jul 5, 2017 at 12:13
-
Microsoft SQL Server Management StudioGeorgeB– GeorgeB2017-07-05 12:17:55 +00:00Commented Jul 5, 2017 at 12:17
Add a comment
|
3 Answers
For MS SQL you can use NEWID and CHECKSUM functions like:
UPDATE Users
SET Gender = (CASE WHEN ABS(CHECKSUM(NEWID()) % 2) = 1 THEN 'M' ELSE 'F' END)
NEWID() will generate random GUID
CHECKSUM() will generate hash of that GUID
ABS() to make it either 1 or 0
WARNING! While some people suggesting to use RAND function - please do not use it for this particular case. The query like this:
UPDATE Users SET Gender = CASE WHEN (RAND() > 0.5) THEN 'M' ELSE 'F' END
.. will result that you have all values either M or either F.
Potentially you can seed RAND function with some value like Id, but distribution of values will be not very good: like first 30-40% all M, then 30-40% all F, then M again.
Comments
Try this so you can decrease "0.5" to improve the chances of being picked if you want the "M" to be more dominant.
SELECT CASE WHEN (RAND() > 0.5) THEN 'M' ELSE 'F' END
3 Comments
Pavel Morshenyuk
If you are updating a table using RAND() - its value will be always the same. So you will have whole column of M or F
Pavel Morshenyuk
Consider this update: UPDATE Users SET Gender = CASE WHEN (RAND() > 0.5) THEN 'M' ELSE 'F' END
Chester Lim
I guess it depends on the update. This will work for a loop.
You can use RAND()function.
Please read the following URL: [enter link description here][1]
https://learn.microsoft.com/en-us/sql/t-sql/functions/rand-transact-sql
