Two ways to compare the update select from MySQL

Work encountered when you need to populate a table with data in another table by corresponding rules

For example

Table 1 A

A1 A2

11 90889

32 31241

12 52123

Table 2 b

B1 B2

11

12

13

Where A1 and B1 are the same field (for example, names are stored)

A2 and B2 are the same fields

Requirement: Now you need to import A2 into B2 via A1,b1

condition:a1,b1 One by one corresponds, and each A1 has corresponding b1 corresponding to it

Example sentence Query method

Update
Balancesheet b
Set
b.cs=
(
Select Stockcode from Company C
where C.id=b.comid
)

Explain

5w Data spents 0.7s

Federated query Mode

Update
Balancesheet b
INNER JOIN
Company C
using (Stockcode)
Set
B.cs=c.stockcode

Explain

Spents 0.8s

Because the updated target table has only one, the optimizer has no way to change the order of the associations. In the case of a suitable index, the subquery is more efficient than the associated query! It's different from our stereotype!

condition:a1,b1 One by one corresponds, not every A1 has corresponding b1 corresponding to it

Example sentence Query method

Update
Balancesheet b
Set
b.cs=
(
Select Stockcode from Company C
where C.id=b.comid
)

After a long wait,

Hint subquery returns more than one result??

[ERR] 1242-subquery returns more than 1 row

It can be extrapolated to the reason that not every A1 can find B1, the optimizer's execution path has changed!

You can only hope for a joint query

Federated query Mode

Update
Balancesheet b
INNER JOIN
Company C
using (Stockcode)
Set
B.cs=c.stockcode

The result is that it can be executed smoothly!! Ok!!

Here we can see different indexes, and different data corresponding conditions have an unpredictable effect on the execution path of SQL.

The stereotype is not necessarily correct!

The efficiency of a SQL statement is generally a desired solution that needs to be tested.

And for the problem, the way of joint query is more general!! Subqueries are logically easy to understand, and some can be used in situations where there is better efficiency.

Two ways to compare the update select from MySQL