MySQL案例 1.1.5 在UPDATE语句里进行条件分支

作者: SQL开发者

MySQL面试 Flink SQL

2022/05/23 23:45

问题:

答案:

蹩脚写法:

这样写法是错的, 虽然看上去对

--- 条件一
UPDATE Salaries
    SET salary = salary * 0.9
WHERE salary >= 300000;
---条件二
UPDATE Salaries
    SET salary = salary * 1.2
WHERE salary >- 250000 AND salary < 28000

高手写法:

注意这里一定要写ELSE, 否则会更新为NULL

UPDATE Salaries
    SET salary = CASE WHEN salary >= 30000
                        THEN salary * 0.9
                      WHEN salary >= 250000 AND salary < 280000
                        THEN salary * 1.2
                      ELSE salary END;