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

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. — yanman1234, Jul 07 '17 at 12:28
Lots of answers in SO try googling mysql both as a target for 'UPDATE' and as a separate source for data — P.Salmon, Jul 07 '17 at 12:30
@P.Salmon Already checked on google and applied different names to table as m1 and m2 as suggested in answers. but still not working — hrishi, Jul 07 '17 at 12:33
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. — yanman1234, Jul 07 '17 at 12:38
For Googlers: If you're getting the error due to an `IN ()` clause, try this instead: `IN (SELECT * FROM () AS tmp)` — rinogo, Nov 09 '19 at 19:33
This is fixed in MariaDB 10.3.2: https://mariadb.com/kb/en/update/#update-statements-with-the-same-source-and-target — pieroxy, Apr 13 '23 at 13:35

score 111 · Accepted Answer · answered Jul 07 '17 at 12:50

111

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

answered Jul 07 '17 at 12:50

Thorsten Kettner

89,309
7
49
73

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. – Thorsten Kettner Jul 10 '17 at 08:01
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 – hrishi Jul 10 '17 at 08:44
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)`. – Thorsten Kettner Jul 10 '17 at 08:53
ok. Thanks. I will try. Query in answer works much faster than in comment – hrishi Jul 10 '17 at 08:57
5

This is sad. It shows that MySQL's optimizer does a bad job here. – Thorsten Kettner Jul 10 '17 at 09:08
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? – tom10271 Jan 16 '19 at 06:12
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. – DarthRez Jan 31 '20 at 18:48

score 6 · Answer 2 · answered Nov 23 '20 at 10:59

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

Giorgos Betsos · Answer 3 · 2017-07-08T07:33:14.783

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

edited Jul 08 '17 at 07:33

answered Jul 07 '17 at 12:44

Giorgos Betsos

71,379
9
63
98

This is a different statement. You are not checking all finance branch masters, but only those where the manager's status is 1. – Thorsten Kettner Jul 07 '17 at 12:54
@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`. – Giorgos Betsos Jul 07 '17 at 13:00
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. – Thorsten Kettner Jul 07 '17 at 13:34
@ThorstenKettner Yes you are right, I see your point now. I'll try to fix the statement keeping the `EXISTS` operator. – Giorgos Betsos Jul 07 '17 at 17:49
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 – hrishi Jul 10 '17 at 06:26

score 1 · Answer 4 · answered Jul 07 '17 at 12:40

1

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

answered Jul 07 '17 at 12:40

PrinceKayastha

115
6

score 1 · Answer 5 · answered Jun 04 '19 at 14:23

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

user2587656 · Answer 6 · 2020-12-18T12:09:42.370

1

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.

edited Dec 18 '20 at 12:09

answered Dec 11 '20 at 12:56

user2587656

309
3
5

score -1 · Answer 7 · answered Apr 27 '19 at 16:34

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

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

7 Answers7

Linked

Related