How to Solve MySQL's 'You can't specify target table for update in FROM clause' Error?

Troubleshooting MySQL's "You can't specify target table for update in FROM clause" Error

The Issue:

Encountering the error "You can't specify target table for update in FROM clause" during a MySQL UPDATE statement targeting a table (e.g., pers) indicates a limitation in MySQL's query processing.

Example Scenario:

Let's say you have the following UPDATE query:

UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE (P.chefID IS NOT NULL OR gehalt < ... ); 

This query attempts to update the gehalt column in the pers table based on conditions involving the same pers table, causing the error.

Why This Happens:

MySQL's restriction prevents ambiguous updates where the FROM clause could lead to unintended modifications.

The Solution:

The workaround involves creating a temporary alias of the table using a subquery:

UPDATE pers P
SET P.gehalt = (SELECT T.gehalt * 1.05 FROM (SELECT * FROM pers) AS T WHERE T.persID = P.persID)
WHERE (P.chefID IS NOT NULL OR P.gehalt < ...);

This revised query avoids the direct reference to pers within the UPDATE statement itself. The subquery (SELECT * FROM pers) AS T creates a temporary table alias T, allowing the UPDATE statement to reference the necessary data without violating MySQL's rule.

Important Note:

While using SELECT * simplifies the example, for optimal performance, select only the required columns from the pers table in the subquery. Always include a relevant WHERE clause in your subquery to ensure efficient data retrieval. In the example above, WHERE T.persID = P.persID is crucial for correctly matching rows between the main UPDATE statement and the subquery.

The above is the detailed content of How to Solve MySQL's 'You can't specify target table for update in FROM clause' Error?. For more information, please follow other related articles on the PHP Chinese website!