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.