Selectfrom in MySql: target of the link to be updated _ MySQL

Selectfrom in MySql: a link target to be updated bitsCN.com

In MySql, how to select from a link target to be updated:

Problem description:

In the fifth edition of Database System Concepts ( Fifth Edition), Chapter 3, 3.10.3 about SQL updates. An example is as follows:

+ ------------------------- + -------------------- + ------------ +

| Account_number | branch_name | balance |

+ ------------------------ + --------------------- + ------------ +

| A-101 | Downtown | 500.00 |

| A-102 | Perryridge | 400.00 |

| A-201 | Brighton | 900.00 |

| A-215 | Mianus | 700.00 |

| A-217 | Brighton | 750.00 |

| A-222 | Redwood | 700.00 |

| A-305 | Round Hill | 350.00 |

+ ------------------------ + ---------------------- + ------------ +

Updateaccount

Setbalance = balance * 1.05

Wherebalance> (select avg (balance)

Fromaccount );

Then an error is reported! Yes! As follows:

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

The error is that you cannot point to and select a target link to be modified or updated.

Http://dev.mysql.com/doc/refman/5.0/en/update.htmlwrite:

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

However, in many cases, I want to update a link with some data, which happens to be obtained through a subquery pointing to this link, for example, in this example, the aggregate function is used to calculate the balance mean of the account.

Solution:

MySQL will convert the subquery derivative relationship (derivedtable) from the clause into a temporary table (temporarytable). Therefore, we can enclose the (selectavg (balance) from account) into a from clause:

Updateaccount

Setbalance = balance * 1.05

Wherebalance> (select avg (tmp. balance)

From (select * from account) as tmp

)

Refer:

Http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

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

Http://forge.mysql.com/wiki/MySQL_Internals

BitsCN.com