Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

Thank you.

share|improve this question
    
I apologize if this seems forward, but it seems odd that you used the earlier solution proposed to you by @Mikael Eriksson, yet you credited @ Jon Seigel with providing the solution. Why? – STLDeveloper Jul 31 '14 at 19:21
up vote 8 down vote accepted

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

ALTER TABLE MyTable ADD MyColumn int IDENTITY(1, 2) NOT NULL

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

ALTER TABLE MyTable ADD MyColumn uniqueidentifier NOT NULL
    CONSTRAINT DF_MyTable_MyColumn
        DEFAULT (NEWSEQUENTIALID())

This will do three things atomically: 1. Add a column that does not allow NULL values; 2. Create a default constraint for the column; 3. Populate each row in the table using the default constraint.

While this example uses a uniqueidentifier column, it works just as well with any data type and default constraint.


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.

BEGIN TRANSACTION

    ALTER TABLE MyTable ADD MyColumn int NULL

    UPDATE MyTable
        SET MyColumn = ...

    ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL

COMMIT TRANSACTION

Method 4: Populate using a SEQUENCE object

For SQL Server 2012, you can populate a column using values generated by a SEQUENCE object -- I haven't worked with this at all yet, so I will refer to a MSDN article for completeness.

share|improve this answer
    
Thank you all, I used the method 3 with a combination with the first answer : update T set cn = rn from ( select cn, row_number() over(order by (select 1)) as rn from TableX ) T – med_alpa May 21 '12 at 13:08
    
Is ALTER TABLE supported in a transaction? – Bart van Heukelom May 21 '12 at 16:18
    
@Bart: Yes. There may be a couple of edge/corner cases, but the above is certainly supported. ALTER DATABASE, on the other hand, cannot run in a user transaction. – Jon Seigel May 22 '12 at 2:09

The following updates column 'cn' with sequence number starting from 1

DECLARE @id INT 
SET @id = 0 
UPDATE X
SET @id = cn = @id + 1 
GO 

http://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/

share|improve this answer

If you are happy with a number starting from 1 you can use row_number().

update T
set cn = rn
from (
       select cn,
              row_number() over(order by (select 1)) as rn
       from TableX
     ) T
share|improve this answer

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

update invoice set RecNo = (next value for seq_invoice_recno)
where invoiceid in (select top 100000 invoiceid from invoice where RecNo is null 
order by invoiceId)
share|improve this answer

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.

CREATE TABLE sorting (sid numeric(10,10), rn int);

INSERT INTO sorting (sid, rn)
SELECT SortID, RecordNumber FROM Beleg
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID;

UPDATE Beleg SET SortID = (SELECT rn FROM sorting WHERE sid=Beleg.SortID)
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210;

DROP TABLE sorting;
share|improve this answer
    
What is this RecordNumber? Seems like a proprietary Filemaker function/feature. Doesn't apply to SQL Server and not in SQL 92 standard. – ypercubeᵀᴹ Apr 14 at 10:32
    
Yes, you're right. RecordNumber is a calculated column that you can define in Filemaker, that holds the record number of each row when it is displayed on screen and after it is sorted (by Filemaker). – Gary Czychi Apr 20 at 5:51
    
OK, good, didn't know that. But the question here is not tagged with Filemaker so I don't think the answer is relevant. You could use a temp table and fill that column with ROW_NUMBER() function though. – ypercubeᵀᴹ Apr 20 at 6:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.