0

I have a table with name and an id. And for this specified ids its like this

name      id
--------------
BOB       19
Test      20
test2     21

but I want to change in code so its like this

name      id
------------
BOB       1
Test      2
test2     3

Anyone got any suggestions? I want to change identity columns.

When I do this 

set identity_insert Peapole OFF
GO

with CTE as 
(
    select 
        *, row_number () over (order by id) as RN
    from Peapole 
)
update CTE
set id = RN

I get an error:

Cannot update identity column 'id'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sakon
  • 15
  • 3
  • What do you want to achieve by changing unique key. As I understand it is surrogate key. It hasn't and meaning for your domain, it just uniquely identifying your rows. – Hamlet Hakobyan Jan 31 '16 at 11:45
  • 1
    Don't do that, first is stupid to learn this because in the future you will have tables with foreign key relationship and you can't do that, also don't use int for id, use guid. – mybirthname Jan 31 '16 at 12:12
  • 3
    I agree fully with @mybirthname: ID values are supposed to have no other meaning than to provide a unique key. They should ***not*** have any other purpose, like sorting or being without gaps. Whenever you need additional meaning, create a separate column for it. – trincot Jan 31 '16 at 12:15
  • Yes i agree but this is for school i just cant figure it out :) – Sakon Jan 31 '16 at 12:16
  • 2
    Bad advise from school. – trincot Jan 31 '16 at 12:18
  • You can do the following: 1. Create new table "Peapole_Temp" with same columns as first. Be carefull you configure the id of the new table as AUTOINCREMENT and primary key. 2. Copy data from first table to second, but ignoring the id. The database will generate this for you. 3. Delete first table, and 4. Rename second table to "Peapole" – Florian Moser Jan 31 '16 at 12:40

0 Answers0