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

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

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

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