MySQL利用update实现多表更新_mysql 多表update

MySQL利用update实现多表更新

一、数据准备

创建stu（学生）表和certificate（证书）表，并输入数据，代码如下：

create table stu(stu_id int primary key,
       stu_name char(20) not null default '',
       certificate_no char(20) not null default ''
);
insert into stu(stu_id,stu_name) values(10001,'张晓云'),(10002,'王云飞'),
(10003,'李大鹏'),(10004,'王大刚'),(10005,'张小倩'),(10006,'刘明明'),
(10007,'刘涛'),(10008,'张华'),(10009,'陈冰'),(10010,'张静静');

create table certificate(stu_id int primary key,
       certificate_type char(50) not null default '',
       certificate_no char(20) not null default '',
       certificate_date datetime
);

insert into certificate(stu_id,certificate_type,certificate_no,certificate_date) 
values(10001,'计算机二级office高级应用','Comp-2-25879','2018-3-15'),
(10002,'计算机三级网络技术','Comp-3-25666','2018-9-28'),
(10003,'英语四级','CET-4-12458','2018-12-25'),
(10005,'英语四级','CET-4-25487','2018-12-25'),
(10007,'英语六级','CET-6-42156','2019-6-28'),
(10008,'英语六级','CET-6-37455','2019-6-28');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

如果stu表的学生考取的有证书，则把certificate表中的certificate_no填入stu表。

二、使用join关键字建立连接

mysql> update stu s inner join certificate c on s.stu_id=c.stu_id
       set s.certificate_no=c.certificate_no;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from stu;
+--------+-----------+----------------+
| stu_id | stu_name  | certificate_no |
+--------+-----------+----------------+
|  10001 | 张晓云    | Comp-2-25879   |
|  10002 | 王云飞    | Comp-3-25666   |
|  10003 | 李大鹏    | CET-4-12458    |
|  10004 | 王大刚    |                |
|  10005 | 张小倩    | CET-4-25487    |
|  10006 | 刘明明    |                |
|  10007 | 刘涛      | CET-6-42156    |
|  10008 | 张华      | CET-6-37455    |
|  10009 | 陈冰      |                |
|  10010 | 张静静    |                |
+--------+-----------+----------------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

三、不使用join关键字建立连接

mysql> update stu set certificate_no='';
Query OK, 6 rows affected (0.01 sec)
Rows matched: 10  Changed: 6  Warnings: 0

mysql> select * from stu;
+--------+-----------+----------------+
| stu_id | stu_name  | certificate_no |
+--------+-----------+----------------+
|  10001 | 张晓云    |                |
|  10002 | 王云飞    |                |
|  10003 | 李大鹏    |                |
|  10004 | 王大刚    |                |
|  10005 | 张小倩    |                |
|  10006 | 刘明明    |                |
|  10007 | 刘涛      |                |
|  10008 | 张华      |                |
|  10009 | 陈冰      |                |
|  10010 | 张静静    |                |
+--------+-----------+----------------+
10 rows in set (0.01 sec)

mysql> update stu s,certificate c
       set s.certificate_no=c.certificate_no
       where s.stu_id=c.stu_id;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> select * from stu;
+--------+-----------+----------------+
| stu_id | stu_name  | certificate_no |
+--------+-----------+----------------+
|  10001 | 张晓云    | Comp-2-25879   |
|  10002 | 王云飞    | Comp-3-25666   |
|  10003 | 李大鹏    | CET-4-12458    |
|  10004 | 王大刚    |                |
|  10005 | 张小倩    | CET-4-25487    |
|  10006 | 刘明明    |                |
|  10007 | 刘涛      | CET-6-42156    |
|  10008 | 张华      | CET-6-37455    |
|  10009 | 陈冰      |                |
|  10010 | 张静静    |                |
+--------+-----------+----------------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

四、使用子查询

mysql> update stu set certificate_no='';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 6  Changed: 4  Warnings: 0

mysql> select * from stu;
+--------+-----------+----------------+
| stu_id | stu_name  | certificate_no |
+--------+-----------+----------------+
|  10001 | 张晓云    |                |
|  10002 | 王云飞    |                |
|  10003 | 李大鹏    |                |
|  10004 | 王大刚    |                |
|  10005 | 张小倩    |                |
|  10006 | 刘明明    |                |
|  10007 | 刘涛      |                |
|  10008 | 张华      |                |
|  10009 | 陈冰      |                |
|  10010 | 张静静    |                |
+--------+-----------+----------------+
10 rows in set (0.01 sec)

mysql> update stu set stu.certificate_no=
       (select certificate.certificate_no from certificate where certificate.stu_id=stu.stu_id)
       where exists (select 1 from certificate where certificate.stu_id=stu.stu_id);
Query OK, 0 rows affected (0.01 sec)
Rows matched: 6  Changed: 0  Warnings: 0

mysql> select * from stu;
+--------+-----------+----------------+
| stu_id | stu_name  | certificate_no |
+--------+-----------+----------------+
|  10001 | 张晓云    | Comp-2-25879   |
|  10002 | 王云飞    | Comp-3-25666   |
|  10003 | 李大鹏    | CET-4-12458    |
|  10004 | 王大刚    |                |
|  10005 | 张小倩    | CET-4-25487    |
|  10006 | 刘明明    |                |
|  10007 | 刘涛      | CET-6-42156    |
|  10008 | 张华      | CET-6-37455    |
|  10009 | 陈冰      |                |
|  10010 | 张静静    |                |
+--------+-----------+----------------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

五、同时更新两个表中的数据

mysql> update stu set certificate_no='';
Query OK, 6 rows affected (0.01 sec)
Rows matched: 10  Changed: 6  Warnings: 0

mysql> select * from stu;
+--------+-----------+----------------+
| stu_id | stu_name  | certificate_no |
+--------+-----------+----------------+
|  10001 | 张晓云    |                |
|  10002 | 王云飞    |                |
|  10003 | 李大鹏    |                |
|  10004 | 王大刚    |                |
|  10005 | 张小倩    |                |
|  10006 | 刘明明    |                |
|  10007 | 刘涛      |                |
|  10008 | 张华      |                |
|  10009 | 陈冰      |                |
|  10010 | 张静静    |                |
+--------+-----------+----------------+
10 rows in set (0.01 sec)

mysql> update stu s inner join certificate c on s.stu_id=c.stu_id
       set s.certificate_no=c.certificate_no, c.certificate_date='2019-8-8'
       where certificate_type='英语六级';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from stu;
+--------+-----------+----------------+
| stu_id | stu_name  | certificate_no |
+--------+-----------+----------------+
|  10001 | 张晓云    |                |
|  10002 | 王云飞    |                |
|  10003 | 李大鹏    |                |
|  10004 | 王大刚    |                |
|  10005 | 张小倩    |                |
|  10006 | 刘明明    |                |
|  10007 | 刘涛      | CET-6-42156    |
|  10008 | 张华      | CET-6-37455    |
|  10009 | 陈冰      |                |
|  10010 | 张静静    |                |
+--------+-----------+----------------+
10 rows in set (0.00 sec)

mysql> select * from certificate;
+--------+-----------------------------------+----------------+---------------------+
| stu_id | certificate_type                  | certificate_no | certificate_date    |
+--------+-----------------------------------+----------------+---------------------+
|  10001 | 计算机二级office高级应用          | Comp-2-25879   | 2018-03-15 00:00:00 |
|  10002 | 计算机三级网络技术                | Comp-3-25666   | 2018-09-28 00:00:00 |
|  10003 | 英语四级                          | CET-4-12458    | 2018-12-25 00:00:00 |
|  10005 | 英语四级                          | CET-4-25487    | 2018-12-25 00:00:00 |
|  10007 | 英语六级                          | CET-6-42156    | 2019-08-08 00:00:00 |
|  10008 | 英语六级                          | CET-6-37455    | 2019-08-08 00:00:00 |
+--------+-----------------------------------+----------------+---------------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56