Oracle - generate unique random varchar values for each row of a table

Question

I have to update a 'varchar2' column in a table with random values, but the catch is that the column is defined with 'unique' constraint, due to which I am getting the error saying that "Unique constraint violated".

Can somebody help please.

Thanks

why you want to update random value any specific reason – Exhausted Feb 26 '15 at 10:16 — Exhausted, Feb 26 '15 at 10:16
because the existing values are not to be shared – Shaan44 Feb 26 '15 at 10:28 — Shaan44, Feb 26 '15 at 10:28

score 1 · Answer 1 · answered Mar 04 '15 at 08:38

1

You haven't given any constraints over what the values might be, so why not just set it to a set of known unique values, e.g. integers:

UPDATE table_name SET unique_col = TO_CHAR(ROWNUM);

answered Mar 04 '15 at 08:38

Jeffrey Kemp

59,135
14
106
158

Or just use the ROWID if ROWNUM doesn't "look" random enough. – Frank Schmitt Mar 04 '15 at 09:51

score 0 · Accepted Answer · answered Feb 26 '15 at 11:40

you can use the DBMS_RANDOM package to create the random values and insert the same in table like below

INSERT INTO table_name(unique_col) values (DBMS_RANDOM.string('a',n);

here n can be max value of table. For more information refer here http://oracle-base.com/articles/misc/dbms_random.php

The value generated will be unique depends on the length of the value

Oracle - generate unique random varchar values for each row of a table

2 Answers2