Add specific hardcoded values to some rows in a computed column

Question

Is there a possibility to change some values of a computed column? We need it because the values are connected to another system and three of them have to be changed.

Our computed column looks like this:

('Product-'+CONVERT([NVARCHAR](100),(1000)+[Id],(0)))

I now have to change the values Product-1356, Product-1655 and Product-1701 to Product-12, Product-17 and Product-18 (example values). These three have to be hardcoded. The rest has to be computed as it was before.

I've tried updating it normally using T-SQL:

UPDATE MyTable
SET ProductId = 'Product-12'
WHERE ProductId = 'Product-1356'

But now I'm getting the following error:

The column "ProductId" cannot be modified because it is either a computed column or is the result of a UNION operator.

Also updating the Id column didn't work because it's the primary key:

Cannot update identity column 'Id'.

How can I do this? Is it even possible?

Answer 1

Two options I can think of:

1. Delete those rows and re-add them with the correct id.
2. Set up a new column of ProductIdOverride and put any hard coded IDs in there. Leave Null when you want the existing formula to be used. Then change your computed column to COALESCE(ProductIdOverride, ('Product-'+CONVERT([NVARCHAR](100),(1000)+[Id],(0))))