Rand Not Different for Every Row in T-SQL Update

RAND not different for every row in T-SQL UPDATE

Use RAND(CHECKSUM(NEWID()))

• NEWID returns a GUID
• CHECKSUM makes it int, randomly
• The int seeds the RAND

In your case, you could modulo the checkum because CHECKSUM(NEWID()) is already random.

CHECKSUM(NEWID()) % 365

Different rand() value for multiple rows SQL Server

Try this, it's working for me.

UPDATE Team
SET GroupID = CAST(RAND(CHECKSUM(NEWID()))*10000000 AS INT)
WHERE GroupID IN (SELECT GroupID FROM Team
GROUP BY GroupID
HAVING COUNT(GroupID)<4)

This code ensures random values are assigned for all GroupId's in Team table AND no GroupID would have an occurrence of more than 4

How do I generate a random number for each row in a T-SQL select?

Take a look at SQL Server - Set based random numbers which has a very detailed explanation.

To summarize, the following code generates a random number between 0 and 13 inclusive with a uniform distribution:

ABS(CHECKSUM(NewId())) % 14

To change your range, just change the number at the end of the expression. Be extra careful if you need a range that includes both positive and negative numbers. If you do it wrong, it's possible to double-count the number 0.

A small warning for the math nuts in the room: there is a very slight bias in this code. CHECKSUM() results in numbers that are uniform across the entire range of the sql Int datatype, or at least as near so as my (the editor) testing can show. However, there will be some bias when CHECKSUM() produces a number at the very top end of that range. Any time you get a number between the maximum possible integer and the last exact multiple of the size of your desired range (14 in this case) before that maximum integer, those results are favored over the remaining portion of your range that cannot be produced from that last multiple of 14.

As an example, imagine the entire range of the Int type is only 19. 19 is the largest possible integer you can hold. When CHECKSUM() results in 14-19, these correspond to results 0-5. Those numbers would be heavily favored over 6-13, because CHECKSUM() is twice as likely to generate them. It's easier to demonstrate this visually. Below is the entire possible set of results for our imaginary integer range:

Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result:     0 1 2 3 4 5 6 7 8 9 10 11 12 13  0  1  2  3  4  5

You can see here that there are more chances to produce some numbers than others: bias. Thankfully, the actual range of the Int type is much larger... so much so that in most cases the bias is nearly undetectable. However, it is something to be aware of if you ever find yourself doing this for serious security code.

update multiple rows with random 9 digit number using rand() function

As you already figured out yourself, RAND is a run-time constant function in SQL Server. It means that it is called once per statement and the generated value is used for each affected row.

There are other functions that are called for each row. Often people use NEWID usually together with CHECKSUM as a substitute for a random number, but I would not recommend it because the distribution of such random numbers is likely to be poor.

There is a good function specifically designed to generate random numbers: CRYPT_GEN_RANDOM. It is available since at least SQL Server 2008.

It generates a given number of random bytes.

In your case it would be convenient to have a random number as a float value in the range of [0;1], same as the value returned by RAND.

So, CRYPT_GEN_RANDOM(4) generates 4 random bytes as varbinary.
Convert them to int, divide by the maximum value of 32-bit integer (4294967295) and add 0.5 to shift the range from [-0.5;+0.5] to [0;1]:

(CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5)

Your query becomes:

UPDATE SGT_EMPLOYER 
SET SSN = 
    CONVERT(NUMERIC(10,0),
    (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 899999999.0 + 100000000.0)
WHERE EMPLOYER_ACCOUNT_ID = 123456789;

Generate random number between 0.85 and 0.95 for each row in SQL Server

RAND() returns the same number for a given statement. A solution would be to use NEWID(). Something like:

CONVERT( DECIMAL(2, 2), .85 + (.95 - .85 )*RAND(CHECKSUM(NEWID())))

Fiddle here

How to update all rows in a column with random values?

demo: db<>fiddle

The simple one for your JSON is:

UPDATE 
    data_records dr
SET 
    c2 = jsonb_set(dr.c2, '{variation}', to_jsonb(random()));

---

If you want the second column with the generate_series (for whatever) you will need something to join on the original table. generate_series could give you rows from 1 to 5. So to join on the data_records you would need a 1 to 5 column there too. If this is what is saved in c1 there's no problem. Simply join against c1.

But if not you have to generate it, maybe with a row_number window function which adds the row count as column. Then you are able to join the row count against the generated_series column and you have a row with a random value for each c1 and c2. One of them should be unique. This unique column (c1 in my case) works as the WHERE filter of the UPDATE clause. Of course this could be the c2. But if they are not unique you would end with same random values for same c1/c2 values:

UPDATE 
    data_records dr
SET 
    c2 = jsonb_set(dr.c2, '{variation}', to_jsonb(rand.r))
FROM 
    (SELECT *, row_number() OVER () rn FROM data_records) dr_rn
LEFT JOIN
    (SELECT generate_series(1, 5) gs , random() r) rand 
ON dr_rn.rn = rand.gs

WHERE dr.c1 = dr_rn.c1;

It would be really more simple if you would have an unique id column. But nevertheless I don't see any reasons for making this that complicated.

SQL query to update a column from another column randomly without any condition

First of all we must be sure that cardinality of TableB MUST BE greater than or equal to cardinality of TableA.

After the EDIT we assume that cardinality of TableB can also be smaller than cardinality of TableA, so we 1st need to multiply TableB rows enough to be more then TableA rows (tables Cnt and numbers) and then use the numbers table in TblB to multiply the rows.

In this mode, ELEMENT could be repeated across rows, but random part will be different.

We need to add a random info to TableB that will be used both to obfuscate ELEMENT and to shuffle the order. For this purpose we will add a UNIQUEIDENTIFIER column with random values from NEWID() function.

To shuffle the order we will number TableA.ID and the new random column in TableB
adding a sort of interface between them.

To obfuscate ELEMENT we will strip portions of our random info.

;with
Cnt as (
    select cast(ceiling(1.0*CntA.cnt/CntB.cnt) as int) Btimes
    from 
        (select count(*) cnt from TableA) CntA, 
        (select count(*) cnt from TableB) CntB
),
numbers as (
        select top((select Btimes from cnt)) ROW_NUMBER() over (order by object_id) n
        from sys.objects
),
TblB as (
    SELECT *, convert(varbinary(16), NEWID(), 1) rndord
    FROM TableB, numbers
),
TableAx as (
    SELECT *, ROW_NUMBER() over (order by id) idx
    FROM TableA
),
TableBx as (
    SELECT *, ROW_NUMBER() over (order by rndord) idx
    FROM TblB
)
select a.id, ELEMENT + ' ' +
    cast((abs(convert(bigint, rndord)) % 9) as char(1))
    + char(65 + abs(convert(bigint, substring(rndord, 9, 4))) % (90-65)) 
    + char(65 + abs(convert(bigint, substring(rndord, 13, 4))) % (90-65)) postcode
from TableAx a 
left join TableBx b on a.idx = b.idx

This should do the trick

---

Related Topics