If else in SQL (use of case statements)

---- Start

You may be familiar with the if else statement, which is used to control the process. In the world of SQL, case statements have similar effects. The following describes the usage of the case statement. Consider the following situation. Assume that there is a user table, which is defined as follows:

Create Table user <br/> (<br/> name varchar (20) not null, --- name <br/> sex integer, --- gender (1, male 2, female) <br/> birthday date --- birthday <br/> ); 

Case Scenario 1:Export the user table to generate a file with a gender of male or female instead of 1 and 2. What should I do? We can use the following statement for processing:

Select <br/> name, <br/> case sex <br/> when 1 then 'male' <br/> else 'femal' <br/> end as sex, <br/> birthday <br/> from user; <br/> 

Case scenarios 2:Assume that the user has no value at present, and then you have imported a batch of data to the user, but unfortunately, the male is set to 2 by mistake, and the female is set to 1. Now you need to change it, what should I do?

 

Method 1:Use three statements to update 2 to 3, 1 to 2, and 3 to 1, which is troublesome, isn't it?

Update user set sex = 3 where sex = 2; <br/> Update user set sex = 1 where sex = 3; <br/> Update user set sex = 2 where sex = 1; 

Method 2:Use Case statements

Update user set sex = <br/> (<br/> case sex <br/> when 1 then 2 <br/> when 2 then 1 <br/> else sex <br /> end <br/> ); 

Careful friends may have discovered that there is a problem with the execution sequence of the three statements in method 1 above. Yes, I intentionally wrote those statements, only to change 1 to 2, turning 2 into 1 is so troublesome and prone to errors. Imagine what a situation would be if there are many such values that need to be changed. Fortunately, we have case statements, and there are many such values that need to be transformed, so the case statement will not be faulty. Some friends may still have doubts. Will this happen in an endless loop? Haha, the idea is good. If you find that this will lead to an endless loop, you must tell IBM that I didn't find it anyway.

Case 3: Assume that you want to update Michael's birthday to 1949-10-1, and Li's birthday to 1997-7-1. There are many updates like this. What should I do? It's very simple. Most people will do this.

Update user set Birthday = '2014-10-1 'Where name = 'zhang san'; <br/> Update user set Birthday = '2014-7-1' Where name = 'Li si '; 

When the user table has a large amount of data and the name field does not have an index, the full table scan is required for each statement. If there are many such statements, the efficiency will be very poor, in this case, we can use the case statement as follows:

Update user set Birthday = <br/> (<br/> case name <br/> when 'zhang san'then' 1949-10-1 '<br/> when 'Li si' then' 1997-7-1 '<br/> else birthday <br/> end <br/>) <br/> where name in ('zhang san', 'Li si ');

The preceding statement performs a full table scan only once, which is very efficient.

---Thanks:HignyI have found an error in this article, and I would like to express my sincere thanks.

--- For more information, see:DB2 SQL

----Statement: indicate the source for reprinting.

---- Last updated on 2010.5.7

---- Written by shangbo on 2009.9.23

---- End