我在 mysql 中有以下查询,我想检查是否分行编号 and year of finance输入来自分支主控等于分行编号 and year of manager然后更新状态manager表反对分行编号 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”的目标又作为
单独的数据源
这是典型的 MySQL 事情,通常可以通过从派生表中进行选择来规避,即代替
FROM manager AS m2
use
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'
)
);
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)