sql語句之表間字段值複製遇到的一些問題--基於mysql

好久沒來園子了，轉眼2017已經到3月份了，前段時間一直忙沒時間寫部落格（其實是自己懶），感覺內心好慚愧。昨天臨下班前，技術老大突然對我說要改下表結構，問我能不能實現將一個表的字段值複製到另外一個表的某個字段中去，感覺這好拗口，其實就是表間字段值複製。於是，昨晚加了會兒班百度了下然後自己在本地測試了下來，還真弄出來了，下面就把這個sql語句記下來，以備忘。

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.php.cn）！