本文介绍了表被指定两次,既作为"UPDATE"的目标,又作为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'
)
)
)
但出现错误
推荐答案
这是典型的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中数据的单独源的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!