Update Identity column

Question

Update Identity column

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

edwardwill (11/17/2014)
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.

One UPDATE was enough for demo of the QotD.

Igor Micev,My blog: www.igormicev.com

Toreador SSChampion Points: 11365 More actions · Answer 2

Igor Micev (11/17/2014)

Why not?

No reason at all, I was just curious as to what scenario might require it 🙂

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Really nice question. Good way to get the brain moving on a Monday morning.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

logitestus SSCrazy Points: 2928 More actions · Answer 4

This question was pretty simple for me... only because I tried this method of updating the primary keys late last week. My solution was no where even as sophisticated as the above solutions. 🙂

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

Good question, to remind us that allowing an arbitrary insert doesn't mean update too.

Dana Medley SSCertifiable Points: 6764 More actions · Answer 6

twin.devil (11/17/2014)
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. 🙂

+1 Thanks for the great question.

Everything is awesome!

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 7

Nice question, and it could be tricky for anyone that has manually updated an identity value through the GUI. I've done this on occasion and it's not apparent that a new table is actually being created in the background.

Aigle de Guerre!

Miles Neale SSChampion Points: 13147 More actions · Answer 8

Good Question and good answer. Thanks

Not all gray hairs are Dinosaurs!

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 9

stephen.long.1

SSCrazy

Points: 2582

November 17, 2014 at 1:30 pm

#1759075

Great question, Igor, thanks.

Hany Helmy SSChampion Points: 13921 More actions · Answer 10

Ok, good to know that, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

SQLArnold SSCommitted Points: 1847 More actions · Answer 11

Hello Igor,

In the question, are you assuming the correct database has been selected from the drop down menu?

Thanks,

Tim

The pain of Discipline is far better than the pain of Regret!

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 12

Excellent Question! Thanks for sharing!

The SWITCH TO method to update identity column is new to me. Thanks for that too.