2

How can I reset my IDENTITY column in my already populated table?

I tried something like this, but it's not working

WITH TBL AS
(
    SELECT 
        *, 
        ROW_NUMBER() OVER(ORDER BY profile_id) AS RN
    FROM 
        Profiles
)
UPDATE TBL
SET profile_id = RN

ERROR:

Cannot update identity column 'profile_id'.

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Biswa
  • 343
  • 2
  • 4
  • 14
  • 1
    if your profile_id is auto increment, then you cant update it. – Yogesh Sharma Sep 29 '17 at 07:39
  • Drop that col and add again and update with row_number – Ankit Bajpai Sep 29 '17 at 07:41
  • Yes it was set to auto-increment. Not even after doing this SET IDENTITY_INSERT Profiles ON – Biswa Sep 29 '17 at 07:41
  • 3
    If you care about the numerical values present in the identity column, you're probably mis-using it. Treat them as opaque blobs that *happen* to fit in numeric columns and all will be right. Assume/rely on particular values/ordering and you're in for a world of trouble. – Damien_The_Unbeliever Sep 29 '17 at 07:43
  • 3
    Why you need to make such reset? You can always `recalculated` the `id` value using `row_number` when data is read (SELECT). – gotqn Sep 29 '17 at 07:48
  • 1
    If those are real customer email addresses and phone numbers you shouldn't post that image here. Replace it with one not including those columns. – Andy Nichols Sep 29 '17 at 07:54
  • @Andy Nichols. I replaced it with some random numbers and email id. I dn't think these persons really exist – Biswa Sep 29 '17 at 07:59
  • Unclear - do you want to **reset the identity counter**, or did you really intend to **update existing identity values** in a table? The first (resetting identity counter) can be done - carefully - but the second (updating existing identity values in the table) **cannot be done**. – marc_s Sep 29 '17 at 08:19
  • @marc_s the query is an `UPDATE`, so I've assumed he wants to update. – Tanner Sep 29 '17 at 08:21
  • 1
    @Tanner: true - but the title says "reset" so I was wondering which of the two the OP really meant - hence my comment .. – marc_s Sep 29 '17 at 08:22
  • I don't agree: this question is not a duplicate of the other answer. – etsa Sep 29 '17 at 09:30
  • Well let me clear some points. 1. Why you need to make such reset? - Because it being applied on the PRIMARY KEY CONTRAINT which is being referenced by some other tables also. So have having the correct order in the column values is absolutely necessary 2. Now as per many of your suggestions i should drop the column and then add it again updating with row_number which is also not possible as it referenced by other tables unless i make mass alterations in all the related tables So what is the easiest way out of this problem so that i dn't have to make mass alterations – Biswa Sep 29 '17 at 15:35

2 Answers2

7

Using DBCC command "CHECKIDENT" you can reset the identity value of the column in a table.

For example, Identity column has 100 as the last identity. If we want next row to have an identity as 201 then you can achieve this using following DBCC command - 

DBCC CHECKIDENT (N'TableName', RESEED, 34);

If the identity column has to start with an identity of 1 with the next insert then the table should be reseeded with the identity to 0.

But do remember in doing so you might violate the data integrity, the uniqueness of the table records.

Maverick Sachin
  • 874
  • 7
  • 12
0

As far as I know, you can't UPDATE an identity column. If you want to leave the identity (after changing its values) one way is to create another specular table (same fields) and transfer all data into it. Something like this (at the end you'll have a table with a new IDENTITY field).

Original table

CREATE TABLE TESTID (id iNT IDENTITY(1,1), DESCR VARCHAR(100), PROFILEID INT)
INSERT INTO TESTID VALUES ('bbbb', 888);
INSERT INTO TESTID VALUES ('Ccccc', 777);

New table

CREATE TABLE TESTID2 (id INT IDENTITY(1,1), DESCR VARCHAR(100), PROFILEID INT)

Data transfer

SET IDENTITY_INSERT TESTID2 ON
INSERT INTO TESTID2 (ID, DESCR, PROFILEID)
SELECT 100+ROW_NUMBER() OVER (ORDER BY PROFILEID) AS RN, DESCR, PROFILEID FROM TESTID
 SET IDENTITY_INSERT TESTID2 OFF

Test

 INSERT INTO TESTID2 VALUES ('xxxx',999);
 SELECT * FROM TESTID;


id          DESCR              PROFILEID
----------- ----------------- -----------
101         Ccccc               777
102         bbbb                888
103         xxxx                999

If you want, you can change identity starting value using

DBCC checkident ('TESTID2', reseed, 4000)
INSERT INTO TESTID2 VALUES ('yyy',111)

id          DESCR                      PROFILEID
----------- -------------------------- -----------
101         Ccccc                      777
102         bbbb                       888
103         xxxx                       999
4001        yyy                        111

At the end, drop your original table and rename the new one.

etsa
  • 5,020
  • 1
  • 7
  • 18
  • 1
    Tanner has already tagged a duplicate question, where one of the answers provides a simpler way - create a new column instead of table. – Nisarg Shah Sep 29 '17 at 08:10
  • I don't believe this should be the advice, as it's unlikely to help the OP or future visitors. You've not considered any foreign keys that might exist on the identities. I'd say in most dbs, PK's are quite frequently linked to one or many things and doing this where links exist will fail. – Tanner Sep 29 '17 at 08:11
  • @Tanner it was just an example... – etsa Sep 29 '17 at 08:12
  • @etsa Yup, I understand. But if you agree that it is not an improvement over existing answers, you could delete your answer. – Nisarg Shah Sep 29 '17 at 08:13
  • @NisargShah Have you read the answer Tanner suggested? It doesn't seem similar. – etsa Sep 29 '17 at 08:16
  • I am referring to this answer: https://stackoverflow.com/a/666358/5894241. – Nisarg Shah Sep 29 '17 at 08:18
  • @NisargShah In what is it similar to mine? That one doesn't generate another IDENTITY. (I know that method) – etsa Sep 29 '17 at 08:20