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.
Step 1: Generate a temporary table for possible values:
CREATE OR REPLACE TEMPORARY TABLE random_values
AS
SELECT ROW1, ROW2, CONCAT('TEST-' , uniform(10000, 99000, RANDOM())) AS new_value
FROM (SELECT DISTINCT ROW1, ROW2 FROM <table_name>) AS sub;
Step 2: Join using the newly generated and materialized values:
SELECT t.ROW1, rv.new_value AS ROW2
FROM <table_name> t
JOIN random_values rv
ON t.ROW1 = rv.ROW1
AND t.ROW2 = rv.ROW2;
Output:
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 |
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.