How to select from a relationship goal to be updated in MySql

In view of how to select from a relationship goal that will be updated in MySql, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

How to select from a relationship goal that will be updated in MySql:

+-+

| | account_number | branch_name | balance | |

+-- +

| | Amuri 101 | Downtown | 500.00 | |

| | Amuri 102 | Perryridge | 400.00 | |

| | Alam 201 | Brighton | 900.00 | |

| | AME 215 | Mianus | 700.00 | |

| | Amure 217 | Brighton | 750.00 | |

| | Amura 222 | Redwood | 700.00 | |

| | AMEL 305 | Round Hill | 350.00 | |

+-+-- +-+

Updateaccount

Setbalance = balance * 1.05

Wherebalance > (select avg (balance))

Fromaccount)

And then I made a mistake! Is there any! It is as follows:

Youcan't specify target table 'account' for update in FROM clause.

The mistake is that you cannot point to and select a target relationship that will be modified or updated.

Http://dev..com/doc/refman/5.0/en/update.html wrote:

"Currently,you cannot update a table and select from the same table in asubquery."

But in many cases, I want to update a relationship with some data that happens to be obtained through a subquery pointing to that relationship. For example, in this example, I need an aggregate function to calculate the balance mean of account.

Solution:

MySQL materializes the derivative relation (derivedtable) of the subquery of the from clause into a temporary table (temporarytable), so we can insert (selectavg (balance) from account) into another from clause:

Updateaccount

Setbalance = balance * 1.05

Wherebalance > (select avg (tmp.balance))

From (select * from account) as tmp

)

This is the answer to the question about how to select from a relationship goal that will be updated in MySql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.