I added a new column in the Table X
This column "cn" has to be unique and mandatory, but old data don't have any value.
How to update the existing records with sequecely or random unique data?
Even though I think you've created the column already, in this answer I'm going on the assumption that the column does not yet exist. IMO, a unique required column should never be added without planning how to populate the existing rows first. Therefore, I will provide the methods to do this starting from zero.
How you do this depends on what is involved in populating the values.
After whichever method you use, add an unique constraint on the column to ensure data integrity. For Methods 1 and 2, this can be done within the single statement or within a user transaction (not shown), and should be done within the user transaction in Method 3.
There are probably a few other obscure ways of doing this, but I think I've covered the most common.
Method 1: Add an IDENTITY column
This will populate all rows in the table with integer values starting with the seed value (1), increasing by the increment value (2) for every row. I believe the order the values get populated is undefined (if you have to specify an order, use Method 3).
Method 2: Populate using a default constraint
This will do three things atomically: 1. Add a column that does not allow
While this example uses a
Method 3: Populate using an UPDATE statement
This case would occur when, for example, there was a value from another part of your application that needs to be added to the table, or you need to specify an exact order for the unique values.
Method 4: Populate using a SEQUENCE object
For SQL Server 2012, you can populate a column using values generated by a
The following updates column 'cn' with sequence number starting from 1
If you are happy with a number starting from 1 you can use
try this to update using a sequence... You have to do the TOP because of the order by clause in the update statement. I used this statement on SQL SERVER 2012
And if all of this still won't work (maybe because it's plain old SQL-92), you can break this down into multiple steps, as suggested by Ziggy Crueltyfree Zeitgeister, here.