Mysql里添加一列内容是另一个表的对应列

假设我有一个表叫worker，记录运动员的信息，里面的内容是这样的：

mysql> select * from worker;
+----+--------+----------+-----+--------+
| id | name   | team     | job | number |
+----+--------+----------+-----+--------+
|  1 | jordan | bulls    | sg  | NULL   |
|  2 | kobe   | lakers   | sg  | NULL   |
|  3 | shaq   | lakers   | c   | NULL   |
|  4 | curry  | warriors | pg  | NULL   |
|  5 | james  | heats    | sf  | NULL   |
|  6 | harden | rockets  | sg  | NULL   |
|  7 | green  | warriors | pf  | NULL   |
|  8 | rodman | bulls    | pf  | NULL   |
|  9 | pippen | bulls    | sf  | NULL   |
+----+--------+----------+-----+--------+
9 rows in set (0.29 sec)

还有一个表是记录球员跟背号的关系的，叫number：

mysql> select * from number;
+----+---------+--------+
| id | name    | number |
+----+---------+--------+
|  1 | jordan  |     23 |
|  2 | kobe    |     24 |
|  3 | james   |      6 |
|  4 | wade    |      3 |
|  5 | iverson |      3 |
|  6 | shaq    |     34 |
|  7 | curry   |     30 |
|  8 | rodman  |     91 |
|  9 | pippen  |     33 |
| 10 | duncan  |     21 |
| 11 | green   |     23 |
+----+---------+--------+
11 rows in set (0.46 sec)

现在我想让worker的number列的内容是number里对应球员的背号，这个语句怎么办？搞了半天，答案是:

update worker a set a.number = (select b.number from number b where b.name=a.name) where exists (select null from number b where b.name=a.name);

效果如图:

这里要注意一下，如果player表有重复行，比如：

+----+--------+----------+-----+--------+
| id | name   | team     | job | number |
+----+--------+----------+-----+--------+
|  1 | james | cavs    | sf  | NULL   |
|  2 | james | heats   | sf  | NULL   |
|  3 | james | lakers  | sf  | NULL   |
+----+---------+--------+
11 rows in set (0.16 sec)

那么上面语句会报错，提示“返回行不止一行”，这个时候需要添加GROUP BY，过滤重复项:

update worker a set a.number = (select b.number from number b where b.name=a.name GROUP BY b.name) where exists (select null from number b where b.name=a.name);

这样过滤重复项，只留一个James就好了，但是如果number有多个背号，比如詹姆斯穿6号还有23号，那么语句还是要改。