Assigning a unique identity to each record in a table.... 1

[Mayoor]

I current have a table with the following fields

username
password
record_id

the username and passwords are populated, however the record_id for each record is null.

Is there an SQL statement I can use to automatically add a unique reference or will I have to go through each record editing them manually?

eeek please help

Mayoor

 

[vidru]

Probably the easiest way to do it would be to drop the existing record_id column, and re-add it as an IDENTITY column:

alter table TableName drop column record_id

then

alter table TableName  add record_id int identity  (1,1)

-dave

 

[Mayoor]

Dave thats GREAT.

Each record in the users table now has a unique ID.

I have another table called communities which has fields

Community
Username
Password
user_record_id

the user_record_id in now needs to be populated with the unique ID's from the users table, any ideas what the sql statement for this would be?

I have tried

update nsc_community
set user_record_id = (

select nsc_users.record_id from nsc_users
where nsc_users.password = nsc_community.password

but keep getting this message.....

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


:confiused:

Mayoor

 

[dickiebird]

Passwords may not be unique - but username should be
so :

...=(select nsc_users.record_id from nsc_users
where nsc_users.password = nsc_community.password
and nsc_users.username = nsc_community.username)



Dickie Bird (-)))

 

[Mayoor]

ok is there any way of converting a record field which has unique record numbers in it to an identity field so that it auto increments?

 

[SQLSister]

YEs, but it can be messy for several reasons.

First you have to create a new tble and then copy the records into it with identity insert turned off (or you can do this by creating your identity field in Enterprise Manager which basically does this itself every you change a table design from here (that's why design chancges take so much longer in Enterprise manager).

If you have a lot alot of records, Enterprise Manager might time out. In that case you have to do the manual process of
Creating the new table, moving the records into it and then dropping the old table and renaming the new table. Then you will have to check to make sure that all your relationships, constratints etc are there. DOn;t forget triggers.

Enterprise manager is the way to go if you can do it without timing out.

Then of course is the additional problem of the fact that changing an existing id field to an autonumber may break the code you have written against it to insert records. SInce an insert with an identity field cannot specify the value for the identity field unless you turn off the identity insert, any insert statements you already have will break.