Stack Exchange
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 looks correct, but MySQL may be confusing branch_id, year, and type in your last select as m1 variables and thus thinks you're selecting from the same table you're updating, which would cause this error. Try identifying these variables as branch_master.XXX variables and see if that fixes it.

Lots of answers in SO try googling mysql both as a target for 'UPDATE' and as a separate source for data

@yanman1234 Tried but same error

@P.Salmon Already checked on google and applied different names to table as m1 and m2 as suggested in answers. but still not working

Try piecing apart your query to see what part causes the error. Hard code the where values the last select provides. If the error is still there hard code the next value set and so on until the error goes away.

For Googlers: If you're getting the error due to an IN (<query>) clause, try this instead: IN (SELECT * FROM (<query>) AS tmp)

related: https://stackoverflow.com/q/34839842/798677

This is fixed in MariaDB 10.3.2: https://mariadb.com/kb/en/update/#update-statements-with-the-same-source-and-target

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


But why are you looking for other managers then in your update statement? Now it sounds like you simply want to update mangers with finance branch/year: UPDATE manager SET status = 'Y' WHERE (branch_id, year) IN (SELECT branch_id, year FROM branch_master WHERE type = 'finance');. This is completely different from what you have shown.

I tried it before but it is taking too much time to update even single record frm about 30k records. Code in answer works for me with slight modification   I added year in first select

This is strange. If the simple SQL is what you really want, but it is too slow, then add indexes to your tables: create index idx_bm on branch_master(type, branch_id, year) and create index idx_m on manager(branch_id, year).

ok. Thanks. I will try. Query in answer works much faster than in comment

This is sad. It shows that MySQL's optimizer does a bad job here.

Yes it is not working fine. SELECT COUNT(*) FROM tableA is much faster than SELECT COUNT(*) FROM tableA LEFT JOIN tableUselessButHuge ON ..... Mr. Optimizer, you do know left joining tableUselessButHuge is useless and should not be joined right?

I could run my sql in dbBeaver just fine but Magento2 and Zend complained until I grouped the table conflict as suggested "(select * from table) as laim" in my where clause. Sooo Stuupid to use Frameworks that take away native behavior.

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


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

This is a different statement. You are not checking all finance branch masters, but only those where the manager's status is 1.

@ThorstenKettner There is no WHERE status = 1 anywhere in my statement. The correlation will examine all records of manager and perform the UPDATE for those records having an existent branch_id, year pair in branch_master.

Sorry, I was completely confused it seems. It's not about the status, but still ... Let's say there are two manager records: 1) branch=1, year=2016. 2) branch=1, year=2017. And one branch_master record with branch=1, year=2017, type = finance. OP's statement would update both manager records, yours only the second one.

@ThorstenKettner Yes you are right, I see your point now. I'll try to fix the statement keeping the EXISTS operator.

I wan to update record where year will also match. In Thorsten Kettner  example. only one row need to be update with branch =1 and year = 2017

Try :::
UPDATE manager as m1 
SET m1.status = 'Y'
WHERE m1.branch_id IN (
  (SELECT DISTINCT branch_id
  FROM branch_master
  WHERE type = 'finance'))
  AND m1.year IN ((SELECT DISTINCT year
  FROM branch_master 
  WHERE type = 'finance'))


The problem I had with the accepted answer is that create a copy of the whole table, and for me wasn't an option, I tried to execute it but after several hours I had to cancel it.
A very fast way if you have a huge amount of data is create a temporary table:

Create TMP table
CREATE TEMPORARY TABLE tmp_manager
    (branch_id bigint auto_increment primary key,
     year datetime null);
Populate TMP table
insert into tmp_manager (branch_id, year)
select branch_id, year
from manager;
Update with join
UPDATE manager as m, tmp_manager as tmp_m
inner JOIN manager as man on tmp_m.branch_id = man.branch_id
SET status = 'Y'
WHERE m.branch_id = tmp_m.branch_id and m.year = tmp_m.year and m.type = 'finance';


This is by far the fastest way:
UPDATE manager m
INNER JOIN branch_master b on m.branch_id=b.branch_id AND m.year=b.year
SET m.status='Y'
WHERE b.type='finance'

Note that if it is a 1:n relationship the SET command will be run more than once. In this case that is no problem. But if you have something like "SET price=price+5" you cannot use this construction.

Maybe not a solution, but some thoughts about why it doesn't work in the first place:
Reading data from a table and also writing data into that same table is somewhat an ill-defined task.  In what order should the data be read and written? Should newly written data be considered when reading it back from the same table?  MySQL refusing to execute this isn't just because of a limitation, it's because it's not a well-defined task.
The solutions involving SELECT ... FROM (SELECT * FROM table) AS tmp just dump the entire content of a table into a temporary table, which can then be used in any further outer queries, like for example an update query.  This forces the order of operations to be: Select everything first into a temporary table and then use that data (instead of the data from the original table) to do the updates.
However if the table involved is large, then this temporary copying is going to be incredibly slow. No indexes will ever speed up SELECT * FROM table.
I might have a slow day today... but isn't the original query identical to this one, which souldn't have any problems?
UPDATE manager as m1 
SET m1.status = 'Y'
WHERE (m1.branch_id, m1.year) IN (
      SELECT DISTINCT branch_id,year 
        FROM `branch_master` 
       WHERE type = 'finance'
    )

