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:

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

CodePudding user response：

Try using the MERGE statement to populate your new field. It allows you to fully run your query then apply its results as an UPDATE.

CodePudding user response：

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

-- Original

select * from test;

-- 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;