0

I am trying to update the Identity column, and it does not exists in any relational table as of now.

If I run the SQL statement as shown:

SET IDENTITY_INSERT Client..dt_Domain ON;

UPDATE Client..dt_Domain
SET DomainID = 1
WHERE DomainID = 2 AND ClientID = 3

SET IDENTITY_INSERT Client..dt_Domain OFF;

I get the following error:

Msg 8102, Level 16, State 1, Line 6
Cannot update identity column 'DomainID'.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AaBa
  • 451
  • 1
  • 6
  • 21
  • @JamesZ I saw that solution before posting this question, and that is not working for me. – AaBa Feb 10 '17 at 04:35
  • 1
    What is not working specifically about that answer? Because it's correct (and the only real way to do it). If there's nothing in your table where DomainID = 1 then insert all of the same values with a DomainID of 1 and delete where DomainID = 2... – ZLK Feb 10 '17 at 04:37
  • @ZLK I get the same error if I try to insert with indentity as on `Msg 8102, Level 16, State 1, Line 6# # Cannot update identity column 'DomainID'.` – AaBa Feb 10 '17 at 04:43
  • @ZLK To insert records and then delete it was the last solution, if nothing works :) – AaBa Feb 10 '17 at 04:44
  • So you've tried something like `SET IDENTITY_INSERT Client..dt_Domain ON; INSERT Client..dt_Domain(a, b, c) SELECT 1, b, c FROM Client..dt_Domain WHERE a = 2; DELETE Client..dt_Domain WHERE a = 2;SET IDENTITY_INSERT Client..dt_Domain OFF;` and that produced the same error? Because I don't see how that's possible, given you're not updating anything... – ZLK Feb 10 '17 at 04:51
  • I don't know what prb that had, so I just did dummy entries and reached till a point which suffice my DomainID requirement. Thanks for your time @ZLK – AaBa Feb 10 '17 at 05:22
  • 1
    You can **INSERT** explicit values into an identity column with the `SET IDENTITY_INSERT... ON` help - but you can **NEVER UPDATE** an existing value in an identity column. No mechanism, no hack, no "workaround" - it just cannot be done. – marc_s Feb 10 '17 at 05:48
  • Is there a trigger on the table? – Rawheiser Feb 10 '17 at 21:38

1 Answers1

0

I guess there is no way to Update the Identity column.

But instead you can Delete the old record & Insert the same record with your desired Identity number by setting IDENTITY_INSERT ON

Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41
  • I tried with Insert, but it also ended up with same message. Hence, I added the record and then thought of updating it. But now I am stuck again. – AaBa Feb 10 '17 at 04:37
  • Is your Insert violating any constraints like Unique or Primary Key? @AaBa – Shakeer Mirza Feb 10 '17 at 04:39
  • DomainID is the PK and ClientID is the FK, rest there are no keys in that table. – AaBa Feb 10 '17 at 04:48
  • @AaBa There may be a chance of having already a record with `DomainID = 1` – Shakeer Mirza Feb 10 '17 at 04:51
  • 1
    Nope, there were no rows with ID 1. Anyways, I took a donkey approach and moving ahead as I need to fix another 8 tables likes this. Phew! Thanks for your time @Shakeer – AaBa Feb 10 '17 at 05:25