November 16, 2014 at 5:18 am
Comments posted to this topic are about the item Update Identity column
Igor Micev,My blog: www.igormicev.com
November 17, 2014 at 12:39 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 17, 2014 at 1:35 am
Good question, but I had it wrong. I was thinking an UPDATE uses the special 'deleted' and 'inserted' tables. And therefor concluded the INDENTITY_INSERT also applies to UPDATE. I now understand these special tables only are for holding the results of the UPDATE and not how the UPDATE actually works.
Always nice to learn something!!
November 17, 2014 at 1:52 am
Good Question. Easy One.
November 17, 2014 at 2:20 am
Good question, I've learnt something new
November 17, 2014 at 2:31 am
A very nice question and there may be chances of giving the wrong answer as with the "SET IDENTITY_INSERT" one can INSERT data into Identity Column but one can never UPDATE identity column.
November 17, 2014 at 2:32 am
This was removed by the editor as SPAM
November 17, 2014 at 3:09 am
Excellent question. Thanks for sharing.
November 17, 2014 at 3:24 am
The Question came as a real case experience with a software developer, and I had to do the update in that way, ... so thought to share it.
Thanks.
Igor Micev,My blog: www.igormicev.com
November 17, 2014 at 3:30 am
There's one more option to update IDENTITY i.e. using ALTER TABLE ... SWITCH. To be safe intermediate table column is declared as PRIMARY KEY. So original IDENTITY column must be PRIMARY KEY as well otherwise SWITCH will fail.
-- table with the same but identity metadata
-- PRIMARY KEY is needed to keep update safe.
CREATE TABLE RolesNoIdentity
(RoleID INTEGER PRIMARY KEY
, Name VARCHAR(100)
);
--
ALTER TABLE Roles SWITCH TO RolesNoIdentity;
UPDATE dbo.RolesNoIdentity
SET RoleID=-3
WHERE RoleID=-2;
ALTER TABLE RolesNoIdentity SWITCH TO Roles;
DROP TABLE RolesNoIdentity;
Sometimes it may help.
November 17, 2014 at 3:31 am
Igor Micev (11/17/2014)
The Question came as a real case experience with a software developer, and I had to do the update in that way, ... so thought to share it.Thanks.
+1000, So true and QotD is the best place to share it. 🙂
November 17, 2014 at 3:40 am
serg-52 (11/17/2014)
There's one more option to update IDENTITY i.e. using ALTER TABLE ... SWITCH. To be safe intermediate table column is declared as PRIMARY KEY. So original IDENTITY column must be PRIMARY KEY as well otherwise SWITCH will fail.
-- table with the same but identity metadata
-- PRIMARY KEY is needed to keep update safe.
CREATE TABLE RolesNoIdentity
(RoleID INTEGER PRIMARY KEY
, Name VARCHAR(100)
);
--
ALTER TABLE Roles SWITCH TO RolesNoIdentity;
UPDATE dbo.RolesNoIdentity
SET RoleID=-3
WHERE RoleID=-2;
ALTER TABLE RolesNoIdentity SWITCH TO Roles;
DROP TABLE RolesNoIdentity;
Sometimes it may help.
Very good from your side. Thanks.
Igor Micev,My blog: www.igormicev.com
November 17, 2014 at 3:49 am
Out of interest, what was the reason for needing to make this update? I can understand inserts (sort of) due to the need to support legacy data, but not clear on why you'd want to update an identity column?
November 17, 2014 at 4:05 am
The update was incomplete as well. The requirement was to update two rows, so the SQL should have read
SET IDENTITY_INSERT dbo.Roles ON
UPDATE dbo.Roles
SET RoleID=-3
WHERE RoleID=-2;
UPDATE dbo.Roles
SET RoleID=-2
WHERE RoleID=-1;
SET IDENTITY_INSERT dbo.Roles OFF
or something like that. Of course, the UPDATE was going to fail anyway, so the point is moot.
November 17, 2014 at 5:11 am
Toreador (11/17/2014)
Out of interest, what was the reason for needing to make this update? I can understand inserts (sort of) due to the need to support legacy data, but not clear on why you'd want to update an identity column?
Business and analytical requirement related with migration. Why not?
Igor Micev,My blog: www.igormicev.com
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply