【泛微E9】批量更新解决人员的各级部门跟实际部门不匹配

-- 查询不匹配的人员
SELECT H.id,H.LASTNAME,H.DEPARTMENTID
FROM cus_fielddata c  
INNER JOIN hrmresource H ON H.id = c.id
LEFT JOIN view_bmcjpath v ON h.departmentid = v.id 
WHERE  H.STATUS < 5
AND c.scopeid = -1
AND( c.field26 <> v.field1 OR
	c.field27 <> v.field2 OR
	c.field28 <> v.field3 OR
	c.field31 <> v.field4 OR
	c.field32 <> v.field5 OR	
	c.field33 <> v.field6 OR
	(v.field1 IS NULL AND c.field26 IS NOT NULL) OR  
	(v.field2 IS NULL AND c.field27 IS NOT NULL) OR  
	(v.field3 IS NULL AND c.field28 IS NOT NULL) OR  
	(v.field4 IS NULL AND c.field31 IS NOT NULL) OR  
	(v.field5 IS NULL AND c.field32 IS NOT NULL) OR  
	(v.field6 IS NULL AND c.field33 IS NOT NULL)  
)



-- 更新各级部门不匹配的人员
UPDATE cus_fielddata c  
INNER JOIN hrmresource a ON a.id = c.id  
LEFT JOIN view_bmcjpath v ON a.departmentid = v.id  
SET  
    c.field26 = v.field1,  
    c.field27 = v.field2,  
    c.field28 = v.field3,  
    c.field31 = v.field4,  
    c.field32 = v.field5,  
    c.field33 = v.field6  
WHERE  
    c.scopeid = -1  
    AND a.STATUS < 5  
    AND (  
        c.field26 <> v.field1 OR  
        c.field27 <> v.field2 OR  
        c.field28 <> v.field3 OR  
        c.field31 <> v.field4 OR  
        c.field32 <> v.field5 OR  
        c.field33 <> v.field6 OR  
        (v.field1 IS NULL AND c.field26 IS NOT NULL) OR  
        (v.field2 IS NULL AND c.field27 IS NOT NULL) OR  
        (v.field3 IS NULL AND c.field28 IS NOT NULL) OR  
        (v.field4 IS NULL AND c.field31 IS NOT NULL) OR  
        (v.field5 IS NULL AND c.field32 IS NOT NULL) OR  
        (v.field6 IS NULL AND c.field33 IS NOT NULL)  
    )