我们用到最多的update更新数据都是单表更新了，但有的时候我们不得不使用关联多表进行数据更新了，下面我给各位介绍利用upate实现多表关联更新。另外需要注意的是MySQL从4.0.4版本开始才支持执行一个包含多个表的UPDATE的语句。
下面给出我的实验环境
环境不涉及什么业务场景，纯粹为了达到测试语句的效果，所以不要问这么更新有什么意义之类的？在生产环境中修复数据时也是经常需要使用UPDATE多表更新的。测试只是为了给出一些解决问题的思路，另外每次测试时需要把数据还原成原始数据，这个自己搞定了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
# test_pay.user; mysql> select * from test_pay.user; +------+------+----------+ | id | name | phone | +------+------+----------+ | 1 | andy | 10000000 | | 2 | mark | 20000000 | | 3 | dkey | NULL | | 4 | wkey | NULL | +------+------+----------+ 4 rows in set (0.00 sec) # test_space.user; mysql> select * from test_space.user; +------+------+-----------+-----------+ | id | name | phone | address | +------+------+-----------+-----------+ | 1 | andy | 800000000 | shanghai | | 2 | mark | 200000000 | shanghai | | 3 | dkey | 300000000 | beijing | | 4 | siri | 400000000 | zhengzhou | +------+------+-----------+-----------+ 4 rows in set (0.00 sec) |
建表语句如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# test_pay.user; create database test_pay charset utf8mb4; create table test_pay.user(id int,name varchar(10),phone int); insert into test_pay.user values(1,"andy",10000000); insert into test_pay.user values(2,"mark",20000000); insert into test_pay.user values(3,"dkey",NULL); insert into test_pay.user values(4,"wkey",NULL); # test_space.user; create database test_space charset utf8mb4; create table test_space.user(id int,name varchar(10),phone int,address varchar(10)); insert into test_space.user values(1,"andy",800000000,"shanghai"); insert into test_space.user values(2,"mark",200000000,"shanghai"); insert into test_space.user values(3,"dkey",300000000,"beijing"); insert into test_space.user values(4,"siri",400000000,"zhengzhou"); |
一、子查询
根据test_space.user和test_space.user表查询相同用户。
|
1 2 3 4 5 6 7 |
mysql> select name from test_pay.user where name in (select name from test_space.user where phone is not null) and phone is null; +------+ | name | +------+ | dkey | +------+ 1 row in set (0.00 sec) |
PS：这里只是为了演示，更好肯定使用join查询。
二、多表更新
把test_pay.user和test_space.user表的name字段名称相同的作为条件，条件相同的则把对应的test_space.user表的phone字段信息更新至test_pay.user表的phone字段。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> update test_pay.user a,test_space.user b set a.phone=b.phone where a.name=b.name; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from test_pay.user; +------+------+-----------+ | id | name | phone | +------+------+-----------+ | 1 | andy | 800000000 | | 2 | mark | 200000000 | | 3 | dkey | 300000000 | | 4 | wkey | NULL | +------+------+-----------+ 4 rows in set (0.00 sec) |
另外一种方法是使用inner join然后更新也是可以的：
|
1 |
update test_pay.user a inner join test_space.user b on a.name=b.name set a.phone=b.phone; |
三、多表多字段更新
把test_pay.user和test_space.user表的id字段相同的作为条件，条件相同的则把对应的test_space.user表的name字段和phone字段更新至test_pay.user表的name字段和phone字段。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> update test_pay.user a,test_space.user b set a.name=b.name,a.phone=b.phone where a.id=b.id; Query OK, 1 row affected (0.00 sec) Rows matched: 4 Changed: 1 Warnings: 0 mysql> select * from test_pay.user; +------+------+-----------+ | id | name | phone | +------+------+-----------+ | 1 | andy | 800000000 | | 2 | mark | 200000000 | | 3 | dkey | 300000000 | | 4 | siri | 400000000 | +------+------+-----------+ 4 rows in set (0.00 sec) |
四、子查询加多表更新
把test_pay.user和test_space.user表的name字段名称相同的作为条件，条件相同的则把对应的test_space.user表的phone字段信息更新至test_pay.user表的phone字段。多了一个更新条件就是只需要更新test_pay.user表中phone字段为空的记录。
|
1 2 3 |
update test_pay.user a,test_space.user b set a.phone=b.phone where a.id=b.id AND a.id in (select m.id from (select id from test_pay.user where phone is null) m); |
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select * from test_pay.user; +------+------+-----------+ | id | name | phone | +------+------+-----------+ | 1 | andy | 10000000 | | 2 | mark | 20000000 | | 3 | dkey | 300000000 | | 4 | wkey | 400000000 | +------+------+-----------+ 4 rows in set (0.00 sec) |
五、多表删除
搞明白了UPDATE语句，其实DELETE语句中也是一样的道理，但语法使用上还是有差别的。注意，在一个多表删除中，不能使用ORDER BY或LIMIT。
比如下面的根据子查询删除数据。
|
1 |
delete from test_pay.user where name in (select name from test_space.user where phone is not null) and phone is null; |
但最好使用如下join语法来进行此类操作，可以更好地利用数据库特性，比如semi join利用。
|
1 |
delete a from test_pay.user as a join (select name from test_space.user where phone is not null) b on a.name=b.name where a.phone is null; |
待补充。。。
