连表更新sql

-- 最高学历院校为空人员查询
SELECT nd.ID, nd.NAME, edu.NURSE_ID, edu.EDUCATION_NUMBER, edu.EDUCATION, edu.SCHOOL FROM NURSE_DOSSIER nd
        left join (
          SELECT
          edu.*,
          Row_Number ( ) Over ( Partition BY NURSE_ID ORDER BY EDUCATION_NUMBER, CREATE_TIME DESC ) su
          FROM
          NURSE_EDUCATION edu
          WHERE edu.DOSSIER_STATUS = 1 AND edu.VALID_FLAG = 1
          AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_1', 'EDUCATION_NUMBER_7')
        ) edu on nd.ID = edu.NURSE_ID
WHERE nd.DOSSIER_STATUS = 1 AND nd.VALID_FLAG = 1 AND edu.su = 1
AND nd.SCHOOL IS NULL
AND nd.ID IN (SELECT NURSE_ID FROM NURSE_EDUCATION WHERE DOSSIER_STATUS = 1 AND VALID_FLAG = 1 AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_1', 'EDUCATION_NUMBER_7'))
ORDER BY nd.ID;

-- 最高学历院校同步
UPDATE NURSE_DOSSIER d
SET d.SCHOOL = (
        SELECT edu.SCHOOL FROM NURSE_DOSSIER nd
                left join (
                  SELECT
                  edu.*,
                  Row_Number ( ) Over ( Partition BY NURSE_ID ORDER BY EDUCATION_NUMBER, CREATE_TIME DESC ) su
                  FROM
                  NURSE_EDUCATION edu
                  WHERE edu.DOSSIER_STATUS = 1 AND edu.VALID_FLAG = 1
                  AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_1', 'EDUCATION_NUMBER_7')
                ) edu on nd.ID = edu.NURSE_ID
        WHERE nd.DOSSIER_STATUS = 1 AND nd.VALID_FLAG = 1 AND edu.su = 1
        AND nd.SCHOOL IS NULL
        AND nd.ID IN (SELECT NURSE_ID FROM NURSE_EDUCATION WHERE DOSSIER_STATUS = 1 AND VALID_FLAG = 1 AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_1', 'EDUCATION_NUMBER_7'))
        AND d.ID = nd.ID
    ),
    d.UPDATE_ID = '最高学历院校同步', d.UPDATE_TIME = sysdate
WHERE d.DOSSIER_STATUS = 1 AND d.VALID_FLAG = 1
AND EXISTS (
    SELECT 1 FROM NURSE_DOSSIER nd
            left join (
              SELECT
              edu.*,
              Row_Number ( ) Over ( Partition BY NURSE_ID ORDER BY EDUCATION_NUMBER, CREATE_TIME DESC ) su
              FROM
              NURSE_EDUCATION edu
              WHERE edu.DOSSIER_STATUS = 1 AND edu.VALID_FLAG = 1
              AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_1', 'EDUCATION_NUMBER_7')
            ) edu on nd.ID = edu.NURSE_ID
    WHERE nd.DOSSIER_STATUS = 1 AND nd.VALID_FLAG = 1 AND edu.su = 1
    AND nd.SCHOOL IS NULL
    AND nd.ID IN (SELECT NURSE_ID FROM NURSE_EDUCATION WHERE DOSSIER_STATUS = 1 AND VALID_FLAG = 1 AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_1', 'EDUCATION_NUMBER_7'))
    AND d.ID = nd.ID
);
COMMIT;

-- 最高学历院校同步回滚为空（回滚用可忽略）
-- UPDATE NURSE_DOSSIER SET SCHOOL = NULL WHERE UPDATE_ID = '最高学历院校同步';COMMIT;

-- 第一学历为空人员查询
SELECT nd.ID, nd.NAME, edu.NURSE_ID, edu.EDUCATION_NUMBER, edu.EDUCATION FROM NURSE_DOSSIER nd
        left join (
          SELECT
          edu.*,
          Row_Number ( ) Over ( Partition BY NURSE_ID ORDER BY EDUCATION_NUMBER, CREATE_TIME DESC ) su
          FROM
          NURSE_EDUCATION edu
          WHERE edu.DOSSIER_STATUS = 1 AND edu.VALID_FLAG = 1
          AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_2', 'EDUCATION_NUMBER_7')
        ) edu on nd.ID = edu.NURSE_ID
WHERE nd.DOSSIER_STATUS = 1 AND nd.VALID_FLAG = 1 AND edu.su = 1
AND nd.FIRST_EDUCATION IS NULL
AND nd.ID IN (SELECT NURSE_ID FROM NURSE_EDUCATION WHERE DOSSIER_STATUS = 1 AND VALID_FLAG = 1 AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_2', 'EDUCATION_NUMBER_7'))
ORDER BY nd.ID;



-- 第一学历同步
UPDATE NURSE_DOSSIER d
SET d.FIRST_EDUCATION = (
        SELECT edu.EDUCATION FROM NURSE_DOSSIER nd
                left join (
                  SELECT
                  edu.*,
                  Row_Number ( ) Over ( Partition BY NURSE_ID ORDER BY EDUCATION_NUMBER, CREATE_TIME DESC ) su
                  FROM
                  NURSE_EDUCATION edu
                  WHERE edu.DOSSIER_STATUS = 1 AND edu.VALID_FLAG = 1
                  AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_2', 'EDUCATION_NUMBER_7')
                ) edu on nd.ID = edu.NURSE_ID
        WHERE nd.DOSSIER_STATUS = 1 AND nd.VALID_FLAG = 1 AND edu.su = 1
        AND nd.FIRST_EDUCATION IS NULL
        AND nd.ID IN (SELECT NURSE_ID FROM NURSE_EDUCATION WHERE DOSSIER_STATUS = 1 AND VALID_FLAG = 1 AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_2', 'EDUCATION_NUMBER_7'))
        AND d.ID = nd.ID
    ),
    d.UPDATE_ID = '第一学历同步', d.UPDATE_TIME = sysdate
WHERE d.DOSSIER_STATUS = 1 AND d.VALID_FLAG = 1
AND EXISTS (
    SELECT 1 FROM NURSE_DOSSIER nd
            left join (
              SELECT
              edu.*,
              Row_Number ( ) Over ( Partition BY NURSE_ID ORDER BY EDUCATION_NUMBER, CREATE_TIME DESC ) su
              FROM
              NURSE_EDUCATION edu
              WHERE edu.DOSSIER_STATUS = 1 AND edu.VALID_FLAG = 1
              AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_2', 'EDUCATION_NUMBER_7')
            ) edu on nd.ID = edu.NURSE_ID
    WHERE nd.DOSSIER_STATUS = 1 AND nd.VALID_FLAG = 1 AND edu.su = 1
    AND nd.FIRST_EDUCATION IS NULL
    AND nd.ID IN (SELECT NURSE_ID FROM NURSE_EDUCATION WHERE DOSSIER_STATUS = 1 AND VALID_FLAG = 1 AND EDUCATION_NUMBER IN ('EDUCATION_NUMBER_2', 'EDUCATION_NUMBER_7'))
    AND d.ID = nd.ID
);
COMMIT;

-- 第一学历同步回滚为空（回滚用可忽略）
-- UPDATE NURSE_DOSSIER SET FIRST_EDUCATION = NULL WHERE UPDATE_ID = '第一学历同步';COMMIT;