表被指定两次，既作为 'UPDATE' 的目标，也作为 mysql 中数据的单独源

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

提问by hrishi

I have below query in mysql where I want to check if branch idand yearof financetype from branch_masterare equal with branch idand yearof managerthen update status in managertable against branch idin manager

我有以下MySQL查询，我要检查，如果分支ID和年度的财务从类型branch_master与平等的分支ID和年的经理，然后更新状态在管理表中对分公司的ID在经理

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

表 'm1' 被指定两次，既作为 'UPDATE' 的目标，也作为单独的数据源

回答by Thorsten Kettner

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

这是一个典型的 MySQL 事情，通常可以通过从派生表中选择来规避，即代替

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

回答by Giorgos Betsos

Try to use the EXISTSoperator:

尝试使用EXISTS运算符：

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 twiceerror.

注意：查询使用@Thorsten 提出的额外嵌套级别，作为规避表被指定两次错误的一种手段。

Demo here

演示在这里

回答by Damián Rafael Lattenero

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:

如果您有大量数据，一个非常快速的方法是创建一个临时表：

1. Create TMP table

   CREATE TEMPORARY TABLE tmp_manager (branch_id bigint auto_increment primary key, year datetime null);

2. Populate TMP table

   insert into tmp_manager (branch_id, year) select branch_id, year from manager;

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

1. 创建 TMP 表

   创建临时表 tmp_manager（branch_id bigint auto_increment 主键，年份日期时间为空）；

2. 填充 TMP 表

   插入 tmp_manager (branch_id, year) select branch_id, year from manager;

3. 加入更新

   UPDATE manager as m, tmp_manager as tmp_m 内部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 ';

回答by PrinceKayastha

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

回答by jlh

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.

从表中读取数据并将数据写入同一个表中，这在某种程度上是一项定义不明确的任务。应该以什么顺序读取和写入数据？从同一个表中读回新写入的数据时是否应该考虑新写入的数据？MySQL 拒绝执行此操作不仅是因为限制，还因为它不是一个明确定义的任务。

The solutions involving SELECT ... FROM (SELECT * FROM table) AS tmpjust 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 thenuse that data (instead of the data from the original table) to do the updates.

解决方案SELECT ... FROM (SELECT * FROM table) AS tmp只涉及将表的全部内容转储到临时表中，然后可以在任何进一步的外部查询中使用该表，例如更新查询。这迫使操作顺序为：首先将所有内容选择到临时表中，然后使用该数据（而不是原始表中的数据）进行更新。

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.

但是，如果涉及的表很大，那么这种临时复制将非常慢。没有索引会加速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'
    )