4. UPDATE with selectivity according to the conditions.
For example, the following update conditions are available
Employees with salary of more than 5,000 will be reduced by 10%
Employees whose wages are between 2000 and 4600 will increase their wages by 15%.
It is easy to consider choosing to execute the UPDATE statement twice, as shown below
Copy the codeThe code is as follows:
--condition 1
UPDATE Personnel
SET salary = salary * 0.9
WHERE salary >= 5000;
--condition 2
UPDATE Personnel
SET salary = salary * 1.15
WHERE salary >= 2000 AND salary < 4600;
But things are not as simple as imagined, assuming that the personal salary is 5,000 yuan. First, according to Condition 1, the salary is reduced by 10%, which becomes the salary of 4,500. Next, when running the second SQL, because the salary of this person is 4500 and within the range of 2000 to 4600, it needs to be increased by 15%. In the end, the salary of this person is 5175, which not only does not decrease, but increases. If it is executed in reverse, then the salary of 4,600 will instead become a salary reduction. No matter how absurd this rule is, if we want an SQL statement to implement this function, we need to use the Case function. The code is as follows:
Copy the codeThe code is as follows:
UPDATE Personnel
SET salary = CASE WHEN salary >= 5000
THEN salary * 0.9
WHEN salary >= 2000 AND salary < 4600
THEN salary * 1.15
ELSE salary END;
One thing to note here is that the ELSE salary of the last line is necessary. If there is no such line, the salary of people who do not meet these two conditions will be written as NUll, which would be a big deal. The default value of the Else part in the Case function is NULL, which is something to note.
This method can also be used in many places, such as changing the primary key.
Generally speaking, if you want to exchange the primary key, a and b of two pieces of data, you need to go through three processes: temporary storage, copying, and reading back data. If you use the Case function, everything will be much simpler.
| p_key | col_1 | col_2 |
| a | 1 | Zhang San |
| b | 2 | Li Si |
| c | 3 | Wang Wu |
Assume that there is the data as above, the primary key needs to be setaandbExchange with each other. If you use the Case function to implement the code as follows
Copy the codeThe code is as follows:
UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a'
THEN 'b'
WHEN p_key = 'b'
THEN 'a'
ELSE p_key END
WHERE p_key IN ('a', 'b');
The same can also be used to exchange two Unique keys. It should be noted that if the primary key needs to be exchanged, it is probably because the design of this table was not done well at the beginning. It is recommended to check whether the design of the table is appropriate.
5. Check whether the data of the two tables are consistent.
The Case function is different from the DECODE function. In the Case function, you can use BETWEEN, LIKE, IS NULL, IN, EXISTS, etc. For example, using IN and EXISTS, subqueries can be performed to achieve more functions.
The following example shows that there are two tables, tbl_A, tbl_B, and both tables have keyCol columns. Now we compare the two tables. If the data of the keyCol column in tbl_A can be found in the data of the keyCol column in tbl_B, the result is 'Matched', and if not found, the result is 'Unmatched'.
To implement the following function, you can use the following two statements
Copy the codeThe code is as follows:
--When using IN
SELECT keyCol,
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B )
THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;
--When using EXISTS
SELECT keyCol,
CASE WHEN EXISTS ( SELECT * FROM tbl_B
WHERE tbl_A.keyCol = tbl_B.keyCol )
THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;
The results of using IN and EXISTS are the same. NOT IN and NOT EXISTS can also be used, but pay attention to the NULL situation at this time.
6. Use the total function in the Case function
Suppose there is a table below
| Student ID (std_id) | Course ID (class_id) | Course name (class_name) | Majoring in flag (main_class_flg) |
| 100 | 1 | economics | Y |
| 100 | 2 | History | N |
| 200 | 2 | History | N |
| 200 | 3 | archeology | Y |
| 200 | 4 | computer | N |
| 300 | 4 | computer | N |
| 400 | 5 | Chemical | N |
| 500 | 6 | math | N |
Some students chose to take several courses at the same time (100,200) and some students chose only one course (300,400,500). Students who take multiple courses should choose one course as their major and write Y in the major flag. Students who only choose one course majoring in flag is N (in fact, if you write Y, there will be no trouble below. For example, please include it more).
Now we need to query this table according to the following two conditions
- Those who only take one course will return to the ID of that course
- Those who take multiple courses will return to the selected main course ID
The simple idea is to execute two different SQL statements for querying.
Condition 1
Copy the codeThe code is as follows:
--Condition 1: Students who only chose one course
SELECT std_id, MAX(class_id) AS main_class
FROM Studentclass
GROUP BY std_id
HAVING COUNT(*) = 1;
Execution result 1
STD_ID MAIN_class
------ ----------
300 4
400 5
500 6
Condition 2
Copy the codeThe code is as follows:
--Condition 2: Students who choose multiple courses
SELECT std_id, class_id AS main_class
FROM Studentclass
WHERE main_class_flg = 'Y' ;
Execution result 2
STD_ID MAIN_class
------ ----------
100 1
200 3
If we use the Case function, we can solve the problem by just one SQL statement. The details are as follows
Copy the codeThe code is as follows:
SELECT std_id,
CASE WHEN COUNT(*) = 1 --Scenarios for students who only choose one course
THEN MAX(class_id)
ELSE MAX(CASE WHEN main_class_flg = 'Y'
THEN class_id
ELSE NULL END
)
END AS main_class
FROM Studentclass
GROUP BY std_id;
Running results
STD_ID MAIN_class
------ ----------
100 1
200 3
300 4
400 5
500 6
By nesting Case functions in the Case function and using methods such as Case functions in the total function, we can easily solve this problem. Using the Case function gives us greater freedom.
Finally, I would like to remind newbies who use Case functions to not make the following mistakes
Copy the codeThe code is as follows:
CASE col_1
WHEN 1 THEN 'Right'
WHEN NULL THEN 'Wrong'
END
In this statement, the line When Null always returns unknown, so there will never be a Wrong situation. Because this sentence can be replaced by WHEN col_1 = NULL, this is an incorrect usage. At this time, we should choose to use WHEN col_1 IS NULL.
Previous page12Read the full text