在 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中文网其他相关文章!