sql语句之表间字段值复制遇到的一些问题--基于mysql

1，背景和需求

两张表a_user和b_user结构如下：

a_user

+--------+-------------+------+-----+---------+----------------+
| Field    | Type          | Null  |  Key | Default | Extra             |
+--------+-------------+------+-----+---------+----------------+
| id_a     | int(11)       | NO   |  PRI | NULL    | auto_increment|
| a_name| varchar(45)| YES  |        | NULL     |                     |
+--------+-------------+------+-----+---------+----------------+

b_user

+--------+-------------+------+-----+---------+----------------+
| Field    | Type          | Null  | Key  | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id_b     | int(11)       | NO   | PRI  | NULL    |auto_increment|
| a_id     | int(11)       | NO   | MUL | NULL    |                      |
| b_name| varchar(45)| YES  |        | NULL    |                      |
+--------+-------------+------+-----+---------+----------------+

两表间关系：表b_user的a_id外键参考表a_user的主键id_a。

记录分别如下：

a_user

+------+--------+
| id_a | a_name |
+------+--------+
|    1      |            |
|    2      |            |
|    3      |            |
|    4      |            |
+------+--------+

b_user

+------+------+--------+
| id_b | a_id | b_name |
+------+------+--------+
|    1      |    1      | 张三   |
|    2      |    2      | 李四   |
|    3      |    2      | 李四   |
|    4      |    3      | 王五   |
|    5      |    3      | 王五   |
|    6      |    3      | 王五   |
|    7      |    4      | 赵六   |
|    8      |    4      | 赵六   |
+------+------+--------+

需求：将b_user表中b_name字段的值复制到a_user表中的a_name。

2，百度和解决遇到的问题

百度了下，发现用这个sql语句靠点儿谱：

update a_user set a_name = (select b_name from b_user where id_a = a_id);

这个语句大概是指，更新表a_user的a_name字段，将表b_user中b_name字段的值作为值来源，但直接执行上面的语句时mysql会报错如下：

ERROR 1242 (21000): Subquery returns more than 1 row

意思是，update语句期望数据来源行数应该与a_user表中的行数4行是相等的，但是上面的子查询结果却是......，等下，上面的子查询可以执行么？当然不可以。其实上面的子查询也就相当于：

select b_name from b_user left join a_user on a_id = id_a;

但是它返回的结果是8行，与表a_user的行数不同。

（1）剔除数据来源的重复行

那么先解决这个问题，将重复的记录剔除不就可以了么： select distinct a_id, b_name from b_user left join a_user on a_id = id_a; 它返回的结果如下：

+------+--------+
| a_id | b_name |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
|    4 | 赵六   |
+------+--------+

结果为2列，如果执行下面的语句它会报错：

1 update a_user set a_name = (select distinct a_id, b_name from b_user left join a_user on a_id = id_a);
2 ERROR 1241 (21000): Operand should contain 1 column(s)

那么怎么把上面的结果变为只包含b_name的一列呢？

（2）利用distinct按a_id剔除重复行后多了a_id列

这个也好解决，把子查询再嵌套一下就可以了：

select b_name from (select distinct a_id, b_name from b_user left join a_user on a_id = id_a) t;

好，再试下update语句

1 update a_user set a_name = (select b_name from (select distinct a_id, b_name from b_user left join a_user on a_id = id_a) t);
2 ERROR 1242 (21000): Subquery returns more than 1 row

可以看到上面又报了子查询结果与更新行数不一致的问题，奇怪，上面的子查询 select b_name from (select distinct a_id, b_name from b_user left join a_user on a_id = id_a) t; 结果是：

+--------+
| b_name |
+--------+
| 张三   |
| 李四   |
| 王五   |
| 赵六   |
+--------+

不是已经剔除了重复行了吗？

（3）子查询嵌套和sql语句执行顺序

分析下上面的问题：现在有两个子查询select语句，外层的select将内层的select作为数据来源进行查询，内层的select和外层的select单独执行时都可以返回预期的结果，那么为什么执行update时却出现了： ERROR 1242 (21000): Subquery returns more than 1 row ？

下面是我的猜测：update语句的执行是一行一行的，那么当更新第一条记录时，update会期望从select子查询中获取一条对应于第一条记录的数据，也就是update a_user set a_name = 值来源 where id_a = a_id;那么就需要加上where语句来限定：

update a_user set a_name = (select b_name from (select distinct a_id, b_name from b_user left join a_user on a_id = id_a) t where 
t.a_id = id_a);

这下就可以了，结果如下：

+------+--------+
| id_a | a_name |
+------+--------+
|    1     | 张三   |
|    2     | 李四   |
|    3     | 王五   |
|    4     | 赵六   |
+------+--------+

3，结果

先写到这儿吧，最后的语句是

update a_user set a_name = (select b_name from (select distinct a_id, b_name from b_user 
left join a_user on a_id = id_a) t where t.a_id = id_a);

说实话，心里还是没底。这里涉及到了sql嵌套查询、sql语句执行顺序、update语句执行过程等sql知识，总之，靠百度和自己误打误撞算是弄出了条sql，不过我只是在本地上测试了下，没有在生产环境下用，对于这条sql的执行效率啥的更是没有概念，先做个记录，以后再研究下。希望有专门搞数据库的同学能够指点下。

以上就是sql语句之表间字段值复制遇到的一些问题--基于mysql的内容，更多相关内容请关注PHP中文网（www.gxlcms.com）！