Suppose you have a database table with a field that currently contains non-unique values. Now suppose you want to update the aforementioned field to contain only unique values, but you don’t want to update any values that were already unique.
To do this you can use the following SQL query:
UPDATE sometable
SET somefield=somefield||uniquekeyfield
WHERE uniquekeyfield NOT IN
( SELECT max(dup.uniquekeyfield)FROM sometable as dup GROUP BY dup.somefield );
This query will update each non-unique value in somefield to have the matching value from uniquefield concatenated to it. All values that were already unique will stay untouched.
For example, if you had the following table:
id | username --------------- 7 | mike 8 | dave 9 | mike 10 | mike 11 | john 12 | jeff
And you decided that you want to change the username field to be unique, you could run the query and your table would become:
id | username --------------- 7 | mike7 8 | dave 9 | mike9 10 | mike 11 | john 12 | jeff
All usernames are now unique, and only minimal changes have been made.
—————
7 | Mike
8 | Dave
9 | Mike
10 | Mike
11 | John
12 | Jeff