I’m not sure I knew identity column values could not be updated. I ran into this while trying to solve a problem recently and had to check the error I was getting. This post shows what happened.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Setup
A quick setup for you. I need to go to the store soon, so hence, here is my sample table (created and filled by SQL Prompt).
CREATE TABLE Vodka
( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
brandname VARCHAR(100) NOT NULL
, rating TINYINT
);INSERT INTO dbo.Vodka
(
brandname,
rating
)
VALUES
('Grey Goose', 9),
('Belvedere', 8),
('Absolut', 7),
('Smirnoff', 6),
('Stolichnaya', 8),
('Ketel One', 9),
('Tito''s', 8),
('Ciroc', 7),
('Skyy', 6),
('Russian Standard', 7););
I then tried this:
OK, what about IDENTITY_INSERT. I know this isn’t an insert, but I thought this “unlocked” the identity column. It doesn’t work.
I searched on MS Learn and found the UPDATE statement documentation. In here, you can see what it says below. I can’t do this.
The error reference provides no info, but apparently this isn’t a thing.
What’s amazing to me is that in 30 years either I’ve never done this, or I’ve rarely encountered it and forgotten. Either is possible.
In any case, if I want to change this, I likely need to “re-insert” the row with a new value (either take the seed or use identity_insert) and then delete the old one.
Crazy.
SQL New Blogger
I was testing something else and ran across this. I decided it’s a great showcase of me learning something and giving a workaround. I’ll show the workaround in another post, which is actually about the thing I was doing.
Of course, that post needs to change.
This took about 10 minutes to write.