How to update table column Id?

Question

I want to update Student table where student_id is an auto-increment field. But I have deleted some students from the table, now I want to assign the ids again (i.e from 1 to last row value).

Why do you need to update those values? Pay attention if Student Table is referred somewhere else as a foreign key — Galma88, May 28 '15 at 12:45

score 0 · Accepted Answer · answered May 28 '15 at 12:39

0

You need to reset the auto increment for the students table.

ALTER TABLE Student AUTO_INCREMENT = 1

answered May 28 '15 at 12:39

Danish Bhayani

425
3
7

score 0 · Answer 2 · answered May 28 '15 at 12:51

I don't think it is a good practise to update CD of a table. Anyway try yhis:

UPDATE YOUR_TABLE
SET ID = T.NEW_ID 
FROM (SELECT [ID_STUDENT] AS OLD_ID, 
             NEW_ID = ROW_NUMBER() OVER (PARTITION BY [ID_STUDENT] ORDER BY        [ID_STUDENT])) AS T
WHERE ID_STUDENT = T.OLD_ID

How to update table column Id?

2 Answers2