sql语句update语句中嵌套的子查询该怎么写？

Question

更新的表是ccms_case_allot_count，根据原表中的原来存储的collector（中文名字）查找brno1更新到org_code列，查找tlrno1更新到collector_name列（ccms_case_allot_count中的中文名字，名字拼音两列名字他们起的不好，大神凑合看吧）update ccms_case_allot_count co set co.org_code=brno1 ,co.collector_name=tlrno1where co.collector=dirc.tlr_name(select co.org_code,co.collector_name,bctl.brno as brno1,inf.tlrno as tlrno1,inf.tlr_name as tlr_namefrom ccms_case_allot_count coleft join sys_tlr_info inf on co.collector=inf.tlr_nameleft join sys_bctl bctl on inf.brcode=bctl.brcode) as dirc
UPDATE ccms_case_allot_count co,
(
SELECT
co.org_code,co.collector_name,bctl.brno AS brno1,inf.tlrno AS tlrno1,inf.tlr_name AS tlr_name
FROM ccms_case_allot_count co
LEFT JOIN sys_tlr_info inf ON co.collector = inf.tlr_name
LEFT JOIN sys_bctl bctl ON inf.brcode = bctl.brcode
) AS dirc
SET co.org_code = brno1, co.collector_name = tlrno1
WHERE co.collector = dirc.tlr_name
这样才是对的

Answer 1

UPDATE ccms_case_allot_count SET
org_code = BCTL.brno
   ,collector_name = INF.tlrno
FROM ccms_case_allot_count CO
LEFT JOIN sys_tlr_info INF ON CO.collector = inf.tlr_name
LEFT JOIN sys_bctl BCTL ON INF.brcode = BCTL.brcode