CODEWITHSUNDEEP
sqlsql-server
Natalia
Natalia

Reputation: 111

update table rows with different values

I have a table called Customer with several fields, for example FirstName, LastName, Birthdate. Later on I have added a new field, CustomerId. I want to add a sequency number to each row in this table, how could I do it?

The CustomerId should start with 1000000.

Some of the rows already have an CustomerId, a number under 1000000.

I was thinking to create a procedure with a loop with a variable increasing by 1. 

CREATE myFunction()
@id int = 1000000
BEGIN
    FOR EACH row in [tablename]
    BEGIN
       IF CustomerId is null then CustomerId = @id
       SET @id = @id + 1
    END    
END;

Upvotes: 0

Views: 74

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146419

Just add the new column as an Identity, and SQL Server will automatically popiulate it with sequentially increasing integers.

 Alter Table {TABLENAME} 
 ADD CustomerId Identity (100000, 1) Integer NOT NULL

Since you mention that some of the columns already have a CustomerId, then I would add another new column using the above, with a different name, then execute an update query to update the existing CustomerId column where it is not populated in whatever manner you need to do to be consistent with your requirements (are duplicates ok? If not, can you change those existing CustomerId values ? If not, what should other values be when there is a conflict ?, etc.

Upvotes: 1

RosSQL
RosSQL

Reputation: 323

As Charles mentioned, you can add the identity column.

to do this:

alter table CUSTOMERS
add ID int Identity(1,1) constraint PK_Customers primary key

You can also do this without setting it to be the Primary Key with

alter table CUSTOMERS
add ID int Identity(1,1)

Upvotes: 0

Related Questions