Search code examples
sqlrandomconcatenationsnowflake-cloud-data-platformdml

Generating the same random number per column values


I have a table which in which I would like to generate random numbers in a specific format (e.g. TEST-10256). For which I have been using the below:

concat('TEST-' , uniform(10000, 99000, RANDOM()))

However I now want to update a table with these random numbers based on two columns, so the desired outcome would be this:

enter image description here

I am not sure how to keep the same random value per previously matching it on the same values in ROW1 & ROW2.


Solution

  • Based on query from @Lukasz, we can use update statement.

    -- Original

    select * from test;
    
    ROW1 ROW2
    A 12
    A 12
    B 5
    B 5
    C 1
    C 1
    D 10

    -- Update statement

    update test t set t.row2 = t1.new_col
            from (select row1, row2, 
                CONCAT('TEST-' , uniform(10000, 99000, RANDOM())) new_col
                from (select distinct row1, row2 from test)
            )t1
        where t1.row1 = t.row1
            and t1.row2 = t.row2;
    

    -- After update

    select * from test;
    
    ROW1 ROW2
    A TEST-37642
    A TEST-37642
    B TEST-39082
    B TEST-39082
    C TEST-50195
    C TEST-50195
    D TEST-14564