Some problems encountered in copying field values ​​between tables in sql statements - based on mysql

It has been a long time since I came to the garden. In the blink of an eye, March 2017 has arrived. I have been too busy to write a blog some time ago (actually I am lazy), and I feel so ashamed. Yesterday before leaving get off work, the technical boss suddenly told me that I wanted to change the table structure and asked me if I could copy the field values ​​of one table to a certain field of another table. I felt that this was a mouthful, but it was actually an inter-table field. Value copy. So, I worked overtime last night and searched Baidu for a while and then tested it locally. I actually figured it out. I will write down this SQL statement as a reminder.

1, background and requirements

The structures of the two tables a_user and b_user are as follows:

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    |                      |
+--------+-------------+------+-----+---------+----------------+

The relationship between the two tables: the a_id foreign key of table b_user refers to the primary key id_a of table a_user.

The records are as follows:

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      | 赵六   |
+------+------+--------+

Requirement: Copy the value of the b_name field in the b_user table to the a_user table a_name.

2, Baidu and solve the problems encountered

Baidu clicked and found Use this sql statement to get some ideas:

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

This statement probably refers to updating the a_name field of table a_user, using the value of the b_name field in table b_user as the value source, but directly executing the above statement When mysql will report the following error:

##

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

means, the update statement expects the number of data source rows to be the same as the number of rows in the a_user table The number of rows is equal to 4, but the result of the above subquery is..., wait, can the above subquery be executed? Of course not. In fact, the above subquery is equivalent to:

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

But the result it returns is 8 rows, which is the same as the number of rows in table a_user different.

(1) Eliminate duplicate rows from the data source

Then solve this problem first, wouldn’t it be enough to eliminate duplicate records:

select distinct a_id, b_name from b_user left join a_user on a_id = id_a; The result it returns is as follows:

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

The result is 2 columns. If you execute the following statement, it will report an error:

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)

So how to change the above result into a column containing only b_name?

(2) Use distinct to remove duplicate rows by a_id and then add a_id column

This can be easily solved by nesting the subquery Just one click:

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

Okay, try the update statement again

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

You can see that the problem of inconsistency between the subquery results and the number of updated rows has been reported above. Strangely, the above subquery

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

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

Haven’t duplicate rows been eliminated?

(3) Subquery nesting and sql statement execution sequence

Analyze the above problem: Now there are two subqueries In the select statement, the outer select uses the inner select as the data source to query. Both the inner select and the outer select can return the expected results when executed separately. So why does it appear when executing update: ERROR 1242 (21000): Subquery returns more than 1 row?

The following is my guess: the update statement is executed line by line, so when updating the first record, update will expect to obtain a record corresponding to the first record from the select subquery. Recorded data, that is, update a_user set a_name = value source where id_a = a_id; then you need to add a where statement to qualify:

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);

That’s it, the result is as follows:

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

3. Let’s write the result

here first. The last statement is

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);

To be honest, I still feel unsure. This involves SQL nested queries, SQL statement execution sequence, update statement execution process and other SQL knowledge. In short, I relied on Baidu and my own mistakes to figure out a SQL. However, I only tested it locally and did not test it locally. For use in a production environment, I have no idea about the execution efficiency of this SQL. I will make a record first and study it later. I hope that students who specialize in databases can give some advice.

The above are some problems encountered in copying field values ​​between tables in SQL statements - based on mysql content. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!