Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql

Question

I have below query in mysql where I want to check if branch id and year of finance type from branch_master are equal with branch id and year of manager then update status in manager table against branch id in manager

UPDATE manager as m1 
  SET m1.status = 'Y'
  WHERE m1.branch_id IN (
    SELECT m2.branch_id FROM manager as m2 
     WHERE (m2.branch_id,m2.year) IN (
        (
          SELECT DISTINCT branch_id,year 
            FROM `branch_master` 
           WHERE type = 'finance'
        )
     )
  )

but getting error

Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

Answer 1

This is a typical MySQL thing and can usually be circumvented by selecting from the table derived, i.e. instead of

FROM manager AS m2

use

FROM (select * from manager) AS m2

The complete statement:

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'
  )
);

Answer 2

The correct answer is in this SO post.

The problem with here accepted answer is - as was already mentioned multiple times - creating a full copy of the whole table. This is way far from optimal and the most space complex one. The idea is to materialize the subset of data used for update only, so in your case it would be like this:

UPDATE manager as m1
SET m1.status = 'Y'
WHERE m1.branch_id IN (
    SELECT * FROM(
        SELECT m2.branch_id FROM manager as m2 
        WHERE (m2.branch_id,m2.year) IN (
            SELECT DISTINCT branch_id,year 
            FROM `branch_master` 
            WHERE type = 'finance')
    ) t
)

Basically you just encapsulate your previous source for data query inside of

SELECT * FROM (...) t

Answer 3

Try to use the EXISTS operator:

UPDATE manager as m1 
SET m1.status = 'Y'
WHERE EXISTS (SELECT 1
              FROM (SELECT m2.branch_id             
                    FROM branch_master AS bm
                    JOIN manager AS m2
                    WHERE bm.type = 'finance' AND
                        bm.branch_id = m2.branch_id AND
                        bm.year = m2.year) AS t
              WHERE t.branch_id = m1.branch_id); 

Note: The query uses an additional nesting level, as proposed by @Thorsten, as a means to circumvent the Table is specified twice error.

Demo here