Shulou(Shulou.com)05/31 Report--

This article introduces how to test the misunderstanding of update data through the correlation table in mysql. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

There are many misunderstandings about the writing of update relational tables, and I used to make mistakes myself.

The general ways of writing are as follows:

Update test1 set name = (select name from test2 where test1.id=test2.id)

Update test1 a,test2 b set a.name=b.name where a.id=b.id

Update test2 set test2.name= (select name from test1 where test1.id=test2.id) where exists (select * from test1 where test1.id=test2.id)

Generally speaking, these three methods are all right, as long as the name and id fields are unique in test1 and test2.

Let's discuss the changes to the table and the associated table respectively:

First, discuss the repetition of the associated table:

Mysql > select * from test1

+-+ +

| | id | name |

+-+ +

| | 1 | K1 | |

| | 2 | K2 | |

| | 3 | K3 | |

| | 4 | kkk |

| | 4 | K4 | |

| | 4 | K4 | |

+-+ +

6 rows in set (0.00 sec)

Mysql > select * from test2

+-+ +

| | id | name |

+-+ +

| | 1 | 2222 |

| | 2 | 2222 |

| | 3 | 2222 | |

| | 4 | 2222 |

+-+ +

4 rows in set (0.00 sec)

Mysql > update test2 set name = (select test1.name from test1 where test1.id=test2.id)

->

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

Mysql > update test2 a dint Test1 b set a.name=b.name where a.id=b.id

Query OK, 4 rows affected (0.01sec)

Rows matched: 4 Changed: 4 Warnings: 0

Mysql > select * from test2

+-+ +

| | id | name |

+-+ +

| | 1 | K1 | |

| | 2 | K2 | |

| | 3 | K3 | |

| | 4 | kkk |

+-+ +

4 rows in set (0.00 sec)

There are multiple records of id=4 in the test1 table. And the values are not consistent. It is not advisable to use test1 as a driver table at this time. No matter what syntax you use, you will either report an error or just drive the first value of the repeated value of the table.

Then we discuss the situation in which the changed table has redundant values.

The redundant value is relative to changing the condition. For example, only change the ID1-4.. And there is a similar situation of id=5.

Mysql > select * from test2

+-+ +

| | id | name |

+-+ +

| | 1 | K1 | |

| | 2 | K2 | |

| | 3 | K3 | |

| | 4 | kkk |

| | 5 | gaopeng |

+-+ +

5 rows in set (0.00 sec)

Mysql > select * from test1

+-+ +

| | id | name |

+-+ +

| | 1 | K1 | |

| | 2 | K2 | |

| | 3 | K3 | |

+-+ +

3 rows in set (0.00 sec)

Mysql > update test2 set test2.name= (select name from test1 where test1.id=test2.id)

Query OK, 2 rows affected (0.01sec)

Rows matched: 5 Changed: 2 Warnings: 0

Mysql > select * from test2

+-+ +

| | id | name |

+-+ +

| | 1 | K1 | |

| | 2 | K2 | |

| | 3 | K3 | |

| | 4 | NULL |

| | 5 | NULL |

+-+ +

5 rows in set (0.00 sec)

As you can see, if the update is unconditional, it will cause the excess data to be changed to null.

Mysql > select * from test2

+-+ +

| | id | name |

+-+ +

| | 1 | rrr |

| | 2 | rrr |

| | 3 | rrr |

| | 4 | rrr |

| | 5 | rrr |

+-+ +

5 rows in set (0.00 sec)

Mysql > update test2 set test2.name= (select name from test1 where test1.id=test2.id) where exists (select * from test1 where test1.id=test2.id)

Query OK, 3 rows affected (0.01sec)

Rows matched: 3 Changed: 3 Warnings: 0

Mysql > select * from test2

+-+ +

| | id | name |

+-+ +

| | 1 | K1 | |

| | 2 | K2 | |

| | 3 | K3 | |

| | 4 | rrr |

| | 5 | rrr |

+-+ +

5 rows in set (0.00 sec)

So pay attention to the conditions that you should pay attention to when associating changes:

1. The driver table cannot have duplicate values. The associated table is used as a reference value, and there can be no repetition, otherwise you don't know which value to take when you take the value.

two。 If there are redundant values in the changed table, conditions must be added, otherwise, the data that is not associated will be changed to null

On the mysql through the related table update data misunderstanding test is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.