Easiest way to update the ids of rows in sql server?

Question

the primary key ID values in this table are being used in our 2 systems that were recently merged, however there is a large number of items in one of the systems that are pointing to the wrong id values, i need to update the ID(PK) values so that the 6 million existing items will be pointing to the correct row.

enter image description here

id like to update the id columns to the following:

ID
1 to 5
2 to 6
3 to 7
4 to 1
5 to 2
6 to 3
7 to 4

Is `ID` a primary key? Why do you want to do this? Do you expect to have to do something like this regularly? — Jeff Rosenberg, Apr 08 '13 at 14:03
If you are using the `ID` column to order the results, then don't - have a specific column for ordering. I'm struggling to think of another reason why you would care what the ID number is. Also, is `ID` an `Identity` column? And is anything using it as a foreign key? — RB., Apr 08 '13 at 14:06
yes its a primary key, I have included the reason in my edit. — Farhad-Taran, Apr 08 '13 at 14:06
@xerxes With respect, you have not included the reason. After reading your edit, I'm none the wiser about *why* you care about the ID values - you've merely stated the business impact. — RB., Apr 08 '13 at 14:08
@xerxes Your edit doesn't explain why this is actually necessary. What are you trying to accomplish by changing these IDs? — Jeff Rosenberg, Apr 08 '13 at 14:08
these values are being used in our 2 systems that were recently merged, how ever there is a large number of items in the existing system that are pointing to the wrong id values, i need to update the id values so that the 6 million existing items will be pointing to the correct row. — Farhad-Taran, Apr 08 '13 at 14:11

score 1 · Accepted Answer · answered Apr 08 '13 at 14:18

1

Well, assuming it is not an IDENTITY column (in which case you'll need to set IDENTITY_INSERT to on) then the following should work (see SQLFiddle for example)

UPDATE MyTable 
SET ID = 
    CASE WHEN ID >= 4 SET ID - 3 
         ELSE  ID + 4
    END

answered Apr 08 '13 at 14:18

RB.

36,301
12
91
131

hi, Im getting the following error even though I have set IDENTITY_INSERT to ON "Msg 8102, Level 16, State 1, Line 2 Cannot update identity column 'ID'." – Farhad-Taran Apr 08 '13 at 14:32
1

@Xerxes Please see the accepted answer to [this question](http://stackoverflow.com/questions/3947453/override-identity-column) which explains how to perform an update of an identity column. Basically, you need to create a new row, then delete the old row. – RB. Apr 08 '13 at 14:44

score 0 · Answer 2 · answered Apr 08 '13 at 14:23

Use update query with a case statement

Update tableName set PkId = Case PkId 
   When 1 then 5
   When 2 then 6
   When 3 then 7
   When 4 then 1
   When 5 then 2
   When 6 then 3
   When 7 then 4  End
Where PkId In (1,2,3,4,5,6,7)

If the values in your answer aer just a small subset of the values that need to be change (Do all 6 million need to change?), then you need to Create a mapping table that has the old incorrect value and the new correct value, and use that (with a join) instead of the case statement.

 Update t set PkId = map.NewPkId
 From tablename t 
    Join mappingTable m 
       On m.oldPkId = t.PkId

hi, Im getting the following error even though I have set IDENTITY_INSERT to ON "Msg 8102, Level 16, State 1, Line 2 Cannot update identity column 'ID'." — Farhad-Taran, Apr 08 '13 at 14:42

Easiest way to update the ids of rows in sql server?

2 Answers2