Sql case in update

View All. Rohatash Kumar Updated date Jun 11, The Simple Case expression checks only for equivalent values and cannot contain Boolean expressions. The S earched Case expression contains Boolean expressions or comparison operators. In the above table the student's Id, Name and their marks are given. I guess there's nothing about it that should have made me think that it wouldn't work; I just never tried this technique before.

I love the fact that you can reference values in the database row that you are going to update. Very cool! I always like to see people leaning on thier database a little to do this kind of work for them, many people would simply break that in to two queries, the first being a SELECT and then use CF conditionals to determine the update type. I'm a big fan of having SQL do this stuff for you, as CF developers its very easy to forget that SQL is an entire language all of its own and is MASSIVLY powerfull for this data manipulation stuff, it took me quite a while to earn full respect for SQL as its own language when I first started developing, but once I did you start thinking about things in a very different way.

I'm sure the other servers have something equivalent. Otherwise, you'd probably want to use tsql or a stored proc. I know what you mean. SQL is awesomely powerful. I am still learning new things all the time. Another nice technique that I have found invaluable on occasion is using a join in an update statement. That is awesome! I knew you could do something like that on a View, but I had no idea you could just update with join usage in a standard statement.

I should have guessed you could since the FROM statement is there and joining would making sense if you need to bring data from other locations. It's easy to get lost in SQL but it can really do some neat stuff. It's a little less flexible than using dynamic CF in your queries, but as long as you are only looking to return simple values the CASE statement is very powerful.

This will return the column value if it is not null, or 0 if it is null. This can save a lot of extra CF coding if you happen to be in that situation. SQL can do many magical stuff. I use SQL for all the business logic. I use ColdFusion for presentation. What if I want to "switch" the field name? End Where But I want to do like this: Update xxx Set Case Are you trying to dynamically select the actual column to update? Could this be something that would be done more effectively using the parent language ColdFusion, ASP, etc :.

We have updated state code column using CASE expression. I hope that you will get an idea about how to use CASE expression. I hope you will enjoy these tips while playing with SQL Server. I would like to have feedback from my blog readers. CASE can be used in any statement or clause that allows a valid expression. If these expressions are equivalent, the expression in the THEN clause will be returned. The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

The CASE expression evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE expression receives the results of the expression as its input.

Errors in evaluating these expressions are possible. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.