SQL statement-problems encountered in field value copying between tables-mysql and sqlmysql

SQL statement-problems encountered in field value copying between tables-mysql and sqlmysql

I haven't been in the garden for a long time. In the twinkling of an eye, 2017 has been in February. I have been busy for some time and have no time to write a blog (I am actually lazy). I feel so ashamed of myself. Before leaving work yesterday, the technical boss suddenly told me to change the table structure and asked me if I could copy the Field Values of one table to another table, it is actually copying the field values between tables. So I added a class Baidu last night and tested it locally. I got it and wrote down this SQL statement to be forgotten.

1. Background and requirements

The structure of the two tables a_user and B _user is 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 |
+ -------- + ------------- + ------ + ----- + --------- + ---------------- +

Relationship between two tables: For the_id foreign key of table B _user, refer 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 | Zhang San |
| 2 | 2 | Li Si |
| 3 | 2 | Li Si |
| 4 | 3 | Wang Wu |
| 5 | 3 | Wang Wu |
| 6 | 3 | Wang Wu |
| 7 | 4 | Zhao Liu |
| 8 | 4 | Zhao Liu |
+ ------ + -------- +

Requirement: copy the value of B _name in the B _user table to a_name IN THE a_user table.

 

2. Baidu and Solutions

Baidu found that using this SQL statement relies on the following points:

Update a_user set a_name = (select B _name from B _user where id_a = a_id );

This statement generally refers to updating the_name field of table a_user and using the B _name field value in Table B _user as the value source. However, mysql reports the following error when executing the preceding statement directly:

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

This means that the update statement expects that the number of rows from the data source should be equal to the number of rows in the_user table, but the above subquery results are ......, wait, can the subquery above be executed? Of course not. In fact, the subquery above is equivalent:

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

But it returns 8 rows, which is different from the row number of table a_user.

(1) Remove duplicate rows from data sources

Solve this problem first and remove Repeated Records: select distinct a_id, B _name from B _user left join a_user on a_id = id_a; the returned results are as follows:

+ ------ + -------- +
| A_id | B _name |
+ ------ + -------- +
| 1 | James |
| 2 | Li Si |
| 3 | Wang Wu |
| 4 | Zhao Liu |
+ ------ + -------- +

The result is two columns. If you execute the following statement, an error is returned:

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 can we change the above result to a column containing only B _name?

(2) Remove duplicate rows by a_id using distinct and add the_id Column

This can be solved by nesting subqueries:

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

OK. 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

We can see that the subquery result is inconsistent with the number of updated rows. It is strange that the 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 |
+ -------- +
| James |
| Li Si |
| Wang Wu |
| Zhao Liu |
+ -------- +

Didn't the duplicate Rows be removed?

(3) subquery nesting and SQL statement execution sequence

Analyze the problem above: There are two subquery select statements, the outer select queries the inner select as the data source, the expected results can be returned when the select statements in the inner and outer layers are executed separately. Why ERROR 1242 (21000): Subquery returns more than 1 row occurs during update?

The following is my guess: the execution of the update statement is one row, so when the first record is updated, the update will expect to get a data record corresponding to the first record from the select subquery, that is, update a_user set a_name = value source where id_a = a_id; then you need to add the where statement to limit:

 

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

 

The result is as follows:

+ ------ + -------- +
| Id_a | a_name |
+ ------ + -------- +
| 1 | James |
| 2 | Li Si |
| 3 | Wang Wu |
| 4 | Zhao Liu |
+ ------ + -------- +

 

3. Result

Write it here first. The final 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 have no idea. This involves SQL knowledge such as SQL nested queries, SQL statement execution sequence, and update statement execution process. In short, Baidu and their own mistaken hits have produced an SQL statement, however, I just tested it locally and didn't use it in the production environment. I have no idea about the execution efficiency of this SQL statement. I will make a record and study it later. I hope that some students who specialize in databases can give some advice.

 

References:

How do I modify values of a column in batches? Assign a field corresponding to another table to a field in this table.

 

End