表被指定两次，既作为"UPDATE"的目标，又作为mysql中数据的单独源 [英] Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql

问题描述

我在mysql中有以下查询，我想从 检查财务ID 和财务的年类型branch_master 等于 manager 的分支ID 和年份，然后在针对 manager

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

但出现错误

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

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

推荐答案

这是典型的MySQL，通常可以通过从派生表中进行选择来规避，而不是

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

使用

FROM (select * from manager) AS m2

完整的声明:

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

这篇关于表被指定两次，既作为"UPDATE"的目标，又作为mysql中数据的单独源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！