SQL Case Statement update

Posted on 2013-10-03
Medium Priority
Last Modified: 2013-10-03
I am trying to update a field in my table but the case statement no matter what defaults to the else?   Can anyone help

declare @thckpsbeforE  decimal(18,4)  

update e
        thckavgmove    =  ((thckpspermove + thckmidpermove + thckoppermove) / 3),
       @thckpsbeforE  =  (ABS((thckpspermove + thckmidpermove + thckoppermove) / 3) * 100),
      e. thckpassastm     = (case WHEN @thckpsbeforE >= 40.0) then
from  table e
Question by:steven
LVL 46

Expert Comment

by:Kent Olsen
ID: 39543586
You've misplaced the ending parenthesis.  In the example above there should NOT be a close-paren after 40.0

LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 39543588
>     @thckpsbeforE  ...
You can't combine updating a table and updating a local variable in a single statement.

>case WHEN @thckpsbeforE >= 40.0
Also, SQL can't use the updated value in line 2 of an update in an expression in line 3.
You'll need to pull this off in back-to-back UPDATE statements.

Author Comment

ID: 39543601
I was just looking at the multiple updates and it worked fine...THANKS FOR THE HELP!!!!

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Ready to get certified? Check out some courses that help you prepare for third-party exams.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question