Why Does MySQL Throw "Table Specified Twice" Error When Updating a Table Using a Subquery?

MySQL Error: Table Specified Twice in UPDATE and Source

When attempting to update the manager table using the provided SQL query, MySQL encounters an error: "Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data." This error occurs due to the table manager being referenced twice in the subquery.

The issue can be resolved by modifying the subquery to select from a derived table. Instead of using the alias m2 directly, the query should create a temporary table using the SELECT statement, and then select from that temporary table. The modified query below achieves this:

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 using a derived table, MySQL can properly differentiate between the manager table used in the UPDATE statement and the manager table referenced in the subquery. This eliminates the confusion that led to the error message.

The above is the detailed content of Why Does MySQL Throw "Table Specified Twice" Error When Updating a Table Using a Subquery?. For more information, please follow other related articles on the PHP Chinese website!