MySQL SELECT同時UPDATE同一張表

MySQL不允許SELECT FROM後面指向用作UPDATE的表，有時候讓人糾結。當然，有比建立無休止的暫存資料表更好的辦法。本文解釋如何UPDATE一張表，同時在查詢子句中使用SELECT.
問題描述

假設我要UPDATE的表跟查詢子句是同一張表，這樣做有許多種原因，例如用統計資料更新表的欄位（此時需要用group子句返回統計值），從某一條記錄的欄位update另一條記錄，而不必使用非標準的語句，等等。舉個例子：

create table apples(variety char(10) primary key, price int);insert into apples values('fuji', 5), ('gala', 6);update apples    set price = (select price from apples where variety = 'gala')    where variety = 'fuji';

錯誤提示是：ERROR 1093 (HY000): You can't specify target table 'apples' for update in FROM clause. MySQL手冊UPDATE documentation這下面有說明 : “Currently, you cannot update a table and
select from the same table in a subquery.”
在這個例子中，要解決問題也十分簡單，但有時候不得不通過查詢子句來update目標。好在我們有辦法。

解決辦法

既然MySQL是通過暫存資料表來實現FROM子句裡面的巢狀查詢，那麼把巢狀查詢裝進另外一個巢狀查詢裡，可使FROM子句查詢和儲存都是在暫存資料表裡進行，然後間接地在外圍查詢被引用。下面的語句是正確的：

update apples   set price = (      select price from (         select * from apples      ) as x      where variety = 'gala')   where variety = 'fuji';

如果你想瞭解更多其中的機制，請閱讀MySQL Internals Manual相關章節。
沒有解決的問題

一個常見的問題是，IN()子句最佳化廢品，被重寫成相關的巢狀查詢，有時（往往？）造成效能低下。把巢狀查詢裝進另外一個巢狀查詢裡並不能阻止它重寫成相關嵌套，除非我下狠招。這種情況下，最好用JOIN重構查詢（rewrite
such a query as a join）。

另一個沒解決的問題是暫存資料表被引用多次。“裝進巢狀查詢”的技巧無法解決這些問題，因為它們在編譯時間被建立，而上面討論的update問題是在運行時。（譯者註：個人認為跟文章討論的主題沒幾毛錢關係）

轉載請註明出處 http://blog.csdn.net/afeiqiang/article/details/8589535

原文地址 http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/