批量更新数据实战（用库里一张表的数据去更新另一张表的字段）

#  需求：将  girs 表的  boy_name 字段 赋值（批量更新）
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     |
# +--+------+---+----+-------+-------------------+----+---------------+

select  * from girls ;

# +--+----+------+---+--------+
# |id|name|boy_id|sex|boy_name|
# +--+----+------+---+--------+
# |1 |aaa |1     |W  |NULL    |
# |2 |bbb |2     |W  |NULL    |
# |3 |ccc |3     |W  |NULL    |
# |4 |4   |4     |W  |NULL    |
# |5 |5   |5     |W  |NULL    |
# |6 |6   |6     |W  |NULL    |
# |7 |7   |7     |W  |NULL    |
# |8 |8   |8     |W  |NULL    |
# |9 |9   |9     |W  |NULL    |
# |10|10  |10    |W  |NULL    |
# |11|11  |11    |W  |NULL    |
# |12|12  |12    |W  |NULL    |
# +--+----+------+---+--------+

# 批量更新写法 ①
# 语法
# update ( A inner | left  join B  on A.id = B.A_id ) set column = value where ...
update ( girls inner   join boys  on girls.boy_id = boys.id)
set girls.boy_name = boys.name ;

# 查看结果
select  * from girls ;

# +--+----+------+---+--------+
# |id|name|boy_id|sex|boy_name|
# +--+----+------+---+--------+
# |1 |aaa |1     |W  |慕容皝     |
# |2 |bbb |2     |W  |慕容冲     |
# |3 |ccc |3     |W  |慕容垂     |
# |4 |4   |4     |W  |慕容博     |
# |5 |5   |5     |W  |NULL    |
# |6 |6   |6     |W  |慕容复     |
# |7 |7   |7     |W  |NULL    |
# |8 |8   |8     |W  |慕容吹雪    |
# |9 |9   |9     |W  |慕容未来人1  |
# |10|10  |10    |W  |慕容未来人2  |
# |11|11  |11    |W  |慕容吹雪2   |
# |12|12  |12    |W  |慕容复2    |
# +--+----+------+---+--------+

#  清空字段

update girls set boy_name = null   ;

select  * from girls ;

# +--+----+------+---+--------+
# |id|name|boy_id|sex|boy_name|
# +--+----+------+---+--------+
# |1 |aaa |1     |W  |NULL    |
# |2 |bbb |2     |W  |NULL    |
# |3 |ccc |3     |W  |NULL    |
# |4 |4   |4     |W  |NULL    |
# |5 |5   |5     |W  |NULL    |
# |6 |6   |6     |W  |NULL    |
# |7 |7   |7     |W  |NULL    |
# |8 |8   |8     |W  |NULL    |
# |9 |9   |9     |W  |NULL    |
# |10|10  |10    |W  |NULL    |
# |11|11  |11    |W  |NULL    |
# |12|12  |12    |W  |NULL    |
# +--+----+------+---+--------+

# 写法 ② (这种写法更简洁) 类似 exsist

update girls set boy_name = (select name from boys where boys.id = girls.boy_id ) ;

#
select  * from girls ;

# +--+----+------+---+--------+
# |id|name|boy_id|sex|boy_name|
# +--+----+------+---+--------+
# |1 |aaa |1     |W  |慕容皝     |
# |2 |bbb |2     |W  |慕容冲     |
# |3 |ccc |3     |W  |慕容垂     |
# |4 |4   |4     |W  |慕容博     |
# |5 |5   |5     |W  |NULL    |
# |6 |6   |6     |W  |慕容复     |
# |7 |7   |7     |W  |NULL    |
# |8 |8   |8     |W  |慕容吹雪    |
# |9 |9   |9     |W  |慕容未来人1  |
# |10|10  |10    |W  |慕容未来人2  |
# |11|11  |11    |W  |慕容吹雪2   |
# |12|12  |12    |W  |慕容复2    |
# +--+----+------+---+--------+