如何解决MySQL中'您无法在FROM子句中指定更新目标表”错误？

解决MySQL“You can'tspecify target table for update in FROM Clause”错误

在 MySQL UPDATE 查询过程中遇到错误“You can't指定目标表在 FROM 子句中更新”？当您尝试在子查询中引用正在更新的表时，通常会发生这种情况。 让我们看一个常见的场景：

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>

这澄清了问题并提供了替代解决方案。 请记住仅选择必要的列以获得最佳性能。

以上是如何解决MySQL中'您无法在FROM子句中指定更新目标表”错误？的详细内容。更多信息请关注PHP中文网其他相关文章！