关于 update 多表关联

优化前

UPDATE cpgl_dzcp 
SET ys_tgdw_dm = cu.org_id 
FROM
    core_user cu 
WHERE
    cpgl_dzcp.create_user = cu.login_id 
    AND cpgl_dzcp.id IN ( SELECT id FROM d2d_dlm_task_data WHERE STATUS = 'Y' AND task_instance_id = '8a8889ba76adf0b90176b1428463001f' ) 
    OR cpgl_dzcp.cp_hx_bh IN ( SELECT cell_a FROM d2d_dlm_task_data WHERE STATUS = 'Y' AND task_instance_id = '8a8889ba76adf0b90176b1428463001f' AND cell_a != '' ) 
    AND cu.login_id = cpgl_dzcp.create_user;

UPDATE cpgl_dzcp 
SET ys_tgdw = o.NAME 
FROM
    core_org o 
WHERE
    o.id = ( SELECT cu.org_id FROM core_user cu WHERE cpgl_dzcp.create_user = cu.login_id ) 
    AND cpgl_dzcp.id IN ( SELECT id FROM d2d_dlm_task_data WHERE STATUS = 'Y' AND task_instance_id = '8a8889ba76adf0b90176b1428463001f' ) 
    OR cpgl_dzcp.cp_hx_bh IN ( SELECT cell_a FROM d2d_dlm_task_data WHERE STATUS = 'Y' AND task_instance_id = '8a8889ba76adf0b90176b1428463001f' AND cell_a != '' );
 

 

 

优化后

UPDATE cpgl_dzcp 
SET ys_tgdw_dm = cu.org_id ,ys_tgdw = o.NAME 
FROM
    core_user cu, d2d_dlm_task_data ddtd,core_org o
WHERE
    cpgl_dzcp.create_user = cu.login_id and (cpgl_dzcp.id=ddtd.id or cpgl_dzcp.cp_hx_bh = cell_a ) and  cu.login_id = cpgl_dzcp.create_user
  and o.id = cu.org_id and ddtd.task_instance_id = '8a8889ba76adf0b90176b1428463001f';

结论 ：尽量不要用子查询，效率低。