Why is my calculated column using CASE returning an error?-CodePudding

I am using SQL Server and SSMS. I have a table called 'Correspondence'. It contains a column called 'Type'. I have created an empty column called 'TypeCode'. I'm trying to insert numerical values based on values in the 'Type column'. Here is my code so far:

INSERT INTO Correspondence (TypeCode)
    SELECT 
        CASE
            WHEN [Type] = 'letter' THEN 1
            WHEN [Type] = 'email' THEN 2
            WHEN [Type] = 'User Note' THEN 3
            ELSE 4
        END;

When I execute the code I get the following error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Correspondence.Type" could not be bound.

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Type'.

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Type'.

Any help would be appreciated.

CodePudding user response：

Actually you need an UPDATE statement (not INSERT which would add new rows in the table):

UPDATE Correspondence 
SET TypeCode = CASE [Type]
                 WHEN 'letter' THEN 1
                 WHEN 'email' THEN 2
                 WHEN 'User Note' THEN 3
                 ELSE 4
               END;

But, you could create the new column as a computed column (virtual or persisted) so that you would not need to update anything:

ALTER TABLE Correspondence ADD TypeCode AS 
CASE [Type]
  WHEN 'letter' THEN 1
  WHEN 'email' THEN 2
  WHEN 'User Note' THEN 3
  ELSE 4
END;

See a simplified demo.