case when then else end 在 update 语句 中的使用

#  case  when then else  end  在 update 语句 中的使用
select *
from boys;
# +--+------+---+----+-------+-------------------+----+---------------+
# |id|name  |sex|age |address|created_time       |enum|create_time_str|
# +--+------+---+----+-------+-------------------+----+---------------+
# |1 |慕容皝   |男  |22  |NULL   |1000-05-01 17:43:43|1   |1000-05-01     |
# |2 |慕容冲   |男  |33  |NULL   |1005-05-21 17:43:49|1   |1005-05-21     |
# |3 |慕容垂   |男  |44  |NULL   |1101-05-21 17:43:52|1   |1101-05-21     |
# |4 |慕容博   |男  |55  |NULL   |1121-05-21 17:43:55|1   |1121-05-21     |
# |6 |慕容复   |男  |55  |NULL   |1190-05-21 17:43:58|1   |1190-05-21     |
# |8 |慕容吹雪  |女  |NULL|NULL   |2021-05-22 17:44:01|2   |2021-05-22     |
# |9 |慕容未来人1|男  |NULL|NULL   |2040-05-22 17:44:01|1   |2040-05-22     |
# |10|慕容未来人2|女  |NULL|NULL   |2050-05-22 17:44:01|2   |2050-05-22     |
# |11|慕容吹雪2 |女  |NULL|NULL   |1901-05-22 17:44:01|2   |1901-05-22     |
# |12|慕容复2  |男  |55  |NULL   |1899-05-21 17:43:58|1   |1899-05-21     |
# +--+------+---+----+-------+-------------------+----+---------------+
#  需求 在  age 是 null  的记录上，在原来name 基础上增加 上性别
update boys
set name = (case  when sex = '男' then concat(name ,'男') else  concat(name ,'女')  end  )
where age is null ;


select *
from boys;
# +--+-------+---+----+-------+-------------------+----+---------------+
# |id|name   |sex|age |address|created_time       |enum|create_time_str|
# +--+-------+---+----+-------+-------------------+----+---------------+
# |1 |慕容皝    |男  |22  |NULL   |1000-05-01 17:43:43|1   |1000-05-01     |
# |2 |慕容冲    |男  |33  |NULL   |1005-05-21 17:43:49|1   |1005-05-21     |
# |3 |慕容垂    |男  |44  |NULL   |1101-05-21 17:43:52|1   |1101-05-21     |
# |4 |慕容博    |男  |55  |NULL   |1121-05-21 17:43:55|1   |1121-05-21     |
# |6 |慕容复    |男  |55  |NULL   |1190-05-21 17:43:58|1   |1190-05-21     |
# |8 |慕容吹雪女  |女  |NULL|NULL   |2021-05-22 17:44:01|2   |2021-05-22     |
# |9 |慕容未来人1男|男  |NULL|NULL   |2040-05-22 17:44:01|1   |2040-05-22     |
# |10|慕容未来人2女|女  |NULL|NULL   |2050-05-22 17:44:01|2   |2050-05-22     |
# |11|慕容吹雪2女 |女  |NULL|NULL   |1901-05-22 17:44:01|2   |1901-05-22     |
# |12|慕容复2   |男  |55  |NULL   |1899-05-21 17:43:58|1   |1899-05-21     |
# +--+-------+---+----+-------+-------------------+----+---------------+