Using case in sql server update statement

The CASE expression causes the sales amount to by added to the appropriate bucket by checking the mnth column. Using CASE expressions in this manner simplifies aggregation and reporting. It provides a quick way of transforming normalized data structures into the more common denormalized formats that most business users are accustomed to viewing on reports.

This same basic idea can be used to create many types of summary rows. For example, to produce a summary row by quarter instead of by month, simply modify the CASE expressions as shown below:. SQL Statement 4. Consider, once again, the sample pubs database. The following query checks the royalty percentage by title and places the percentage into a category based on its value:.

SQL Statement 5. WHERE t. THEN 'High'. THEN 'Moderate'. ELSE 'Low'. FROM authors a, titles t, titleauthor ta. WHERE a. In this example, the royalty percentage royaltyper in the titleauthor table is checked and a category is specified for each author based on the royalty percentage returned.

Usage and Syntax. This provides great flexibility because expressions can be used in a wide number of places.

A simple CASE expression checks one expression against multiple values. If these expressions are equivalent, the expression in the THEN clause will be returned. The basic syntax for a simple CASE expressions is shown below:. A searched CASE expression is more feature-laden.

Any help to achieve this with out using cursor? ColValue else T0. Improve this question. Add a comment. Active Oldest Votes. Improve this answer. Arun Udumbassery Arun Udumbassery 19 7 7 bronze badges. Issue is : you are trying to update same row multiple times in the same query. Sahi Sahi 1, 1 1 gold badge 12 12 silver badges 28 28 bronze badges. ColA; This does involve two passes over each table, but the syntax is very simple which is always a bonus. ColC , This is simple, but i will have 12 to 20 columns where i need to set value based on Type from other table.

Writing one update for each column is not a good idea. This is why it helps to include all pertinent details in the original question : — mendosi. I used the same logic. 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. You can see in the above remarks column of the student table which contain Records as Pass or Fail.

Next Recommended Reading.