PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Sunday, October 23, 2022

[FIXED] Why is my calculated column using CASE returning an error?

 October 23, 2022     calculated-columns, case, sql, sql-server, sql-update     No comments   

Issue

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.


Solution

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.



Answered By - forpas
Answer Checked By - Timothy Miller (PHPFixing Admin)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing