How to Resolve MySQL Error 'Table Specified Twice' When Updating and Querying the Same Table?

Error: Table Specified Twice in MySQL Query

In MySQL, when attempting to update a table while simultaneously using it as a data source within the same query, the error "Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data" may arise. This error stems from MySQL's limitations in handling such queries.

To resolve this issue, instead of using the table's alias directly in the subquery, MySQL requires users to select from a derived table. This involves creating a temporary table based on the original table and referencing the derived table in the subquery.

Revised Query:

Here is a revised version of the query that utilizes a derived table to circumvent the error:

UPDATE manager
SET status = 'Y'
WHERE branch_id IN
(
  SELECT branch_id
  FROM (SELECT * FROM manager) AS m2
  WHERE (branch_id, year) IN
  (
    SELECT branch_id, year
    FROM branch_master
    WHERE type = 'finance'
  )
);

By selecting from the derived table (indicated by "(SELECT * FROM manager) AS m2"), the query avoids the ambiguity that caused the previous error. The derived table acts as an independent data source, enabling the query to reference the table both as a target for the update and as a data source for the subquery.

The above is the detailed content of How to Resolve MySQL Error 'Table Specified Twice' When Updating and Querying the Same Table?. For more information, please follow other related articles on the PHP Chinese website!