如何解決MySQL中「您無法在FROM子句中指定更新目標表」錯誤？

解決MySQL“You can'tspecify target table for update in FROM Clause”錯誤

在 MySQL UPDATE 查詢過程中遇到錯誤「You can't指定目標表在 FROM 子句中更新」？當您嘗試在子查詢中引用正在更新的表時，通常會發生這種情況。 讓我們來看一個常見的場景：

<code class="language-sql">UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE (P.chefID IS NOT NULL
OR gehalt <p>...This approach fails because MySQL prohibits referencing the updated table (pers) inside its own subquery.</p><h2>The Solution: Employing a Temporary Table (or JOIN)</h2><p>The solution involves creating a temporary representation of the table data to avoid directly referencing the `pers` table in the `WHERE` clause.  Here's how:</p>UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE (P.chefID IS NOT NULL
OR gehalt <p>This method uses a `SELECT` statement to create a temporary dataset.  This allows the `WHERE` clause to reference the data without directly referencing the `pers` table being updated.</p><h2>Alternative: Using a JOIN</h2><p>Another effective method is to use a `JOIN` instead of a subquery:</p>UPDATE pers p
INNER JOIN (SELECT chefID, gehalt FROM pers) AS temp ON p.chefID = temp.chefID AND p.gehalt = temp.gehalt
SET p.gehalt = p.gehalt * 1.05
WHERE p.chefID IS NOT NULL OR p.gehalt <p>This approach achieves the same result by joining the `pers` table with itself (aliased as `temp`), thereby avoiding the restriction.</p><h2>Best Practices</h2><p>While using `SELECT *` simplifies the example, selecting only necessary columns and adding a `WHERE` clause to your subquery or `JOIN` significantly improves performance.  Always prioritize efficiency in your database operations.</p></code>

這澄清了問題並提供了替代解決方案。 請記住僅選擇必要的列以獲得最佳效能。

以上是如何解決MySQL中「您無法在FROM子句中指定更新目標表」錯誤？的詳細內容。更多資訊請關注PHP中文網其他相關文章！