这似乎很简单，但我似乎不能把我的头围绕它。。。我正在尝试更新一个表，将列设置为一个查询的结果，该查询使用我正在更新的表中的id列，并使用with（）子句。我还只想更新表中的列，其中fall\u term='fall 2019'
我将提供我的伪代码和我的实际代码。。。
注意：如果我手动输入id列person\u skey的值，我的update语句可以很好地工作。例如，而不是：

WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY

这样做可以：

WHERE V.PERSON_SKEY = 12345

我的代码当前出现错误：

[Error] Execution (10: 27): ORA-00904: "SNP_FACULTY_CENSUS"."PERSON_SKEY": invalid identifier

这是我的伪代码。。

update Table_A 
set Column_A = 
    (select Column_B from Table_B 
    where TABLE_A.id = TABLE_B.id )
where Fall_Term = 'Fall 2019';

这是我的实际代码：

--UPDATE OIR TERMINAL DEGREE IN FALL 2019 SNAPSHOT
UPDATE SNP_FACULTY_CENSUS 
SET OIR_TERMINAL_DEGREE = (
WITH HIGHES_DEG_VALUE AS (
    SELECT max(D.OIR_DEGREE_HIERARCHY) AS MAX_DEG
    FROM VPAA_PPAGGENL_FIS  V
    JOIN DIM_DEGREE_CROSSWALK D
        ON V.VPAA_FACULTY_DEGREE_CODE =
           D.VPAA_DEGREE_CODE       
    WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY) 
SELECT 
CASE 
    WHEN MAX_DEG = 30 
        THEN  --CHECK IF EXIST ON CROSSWALKS AND USE THAT VALUE IF IT DOES
        (CASE
            WHEN EXISTS (SELECT V.VPAA_FACULTY_DEGREE_CODE, --IF ON SUBJ CODE CROSSWALK
                         V.VPAA_FACULTY_SUBJECT_CODE
                         FROM VPAA_PPAGGENL_FIS V
                         JOIN DIM_DEGREE_CROSSWALK D
                         ON V.VPAA_FACULTY_DEGREE_CODE = D.VPAA_DEGREE_CODE 
                         INNER JOIN  SUBJ_CODE_CROSSWALK  S
                         ON V.VPAA_FACULTY_DEGREE_CODE = S.VPAA_FACULTY_DEGREE_CODE
                            and S.VPAA_FACULTY_SUBJECT_CODE = V.VPAA_FACULTY_SUBJECT_CODE 
                            WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY 
                         )
                THEN (SELECT DISTINCT  -- USE TERMINAL VALUE FROM SUBJ CODE
                       CASE
                             WHEN s.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN s.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END
                      FROM SUBJ_CODE_CROSSWALK  S
                                   INNER JOIN VPAA_PPAGGENL_FIS V
                                       ON V.VPAA_FACULTY_DEGREE_CODE =
                                          S.VPAA_FACULTY_DEGREE_CODE
                                    AND S.VPAA_FACULTY_SUBJECT_CODE =
                                          V.VPAA_FACULTY_SUBJECT_CODE
                                    WHERE  V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY )                        

            WHEN EXISTS (SELECT DISTINCT V.VPAA_FACULTY_DEGREE_CODE, --IF ON CLIC INSTRUCTORS CROSSWALK
                                V.VPAA_FACULTY_SUBJECT_CODE,
                                I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG
                         FROM VPAA_PPAGGENL_FIS V
                         INNER JOIN CLIC_INSTRUCTORS_CROSSWALK C
                         ON V.VPAA_FACULTY_DEGREE_CODE = C.VPAA_FACULTY_DEGREE_CODE 
                         AND V.VPAA_FACULTY_SUBJECT_CODE = C.VPAA_FACULTY_SUBJECT_CODE
                         INNER JOIN IPEDS_REPORT_DETAILS I ON V.PIDM = I.HR_PIDM
                         WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY 
                         AND  (I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396000'
                              OR I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396010')
                         ) 
                THEN (SELECT DISTINCT 
                        CASE
                             WHEN C.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN C.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END  
                       FROM CLIC_INSTRUCTORS_CROSSWALK C
                       INNER JOIN VPAA_PPAGGENL_FIS V
                           ON C.VPAA_FACULTY_DEGREE_CODE =
                              V.VPAA_FACULTY_DEGREE_CODE
                        AND C.VPAA_FACULTY_SUBJECT_CODE =
                              V.VPAA_FACULTY_SUBJECT_CODE
                        WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ))
       ELSE --USE DIM DEGREE CROSSWALK NORMALLY
            (SELECT DISTINCT 
                        CASE
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END  
            FROM DIM_DEGREE_CROSSWALK D
                 JOIN VPAA_PPAGGENL_FIS V
                     ON V.VPAA_FACULTY_DEGREE_CODE =
                        D.VPAA_DEGREE_CODE
               WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY)
               ORDER BY OIR_DEGREE_HIERARCHY DESC
               FETCH FIRST ROW ONLY )
        END) --USE DIM DEGREE CROSSWALK FOR THE TERMINAL VALUE FOR THE MAX DEG THERE

    WHEN MAX_DEG != 30
    THEN
        (SELECT DISTINCT 
                        CASE
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END  
            FROM DIM_DEGREE_CROSSWALK D
             JOIN VPAA_PPAGGENL_FIS V
                 ON V.VPAA_FACULTY_DEGREE_CODE =
                    D.VPAA_DEGREE_CODE
           WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY 
           ORDER BY OIR_DEGREE_HIERARCHY DESC
           FETCH FIRST ROW ONLY )
    ELSE NULL
END 
FROM HIGHES_DEG_VALUE ) 
WHERE SNP_FACULTY_CENSUS.OIR_FALL_TERM = 'Fall 2019'
;

表格结构：

CREATE TABLE SNP_FACULTY_CENSUS --(Snapshot table)
(
  OIR_ACADEMIC_RANK                    VARCHAR2(50 BYTE),
  OIR_DEPARTMENT_HOME_ORG              VARCHAR2(50 BYTE),
  OIR_EMPLOYEE_NAME_LAST_FIRST         VARCHAR2(300 BYTE),
  HR_POSN_ESOC                         VARCHAR2(10 BYTE),
  OIR_FT_PT                            VARCHAR2(25 BYTE),
  OIR_FACULTY_STATUS                   VARCHAR2(25 BYTE),
  OIR_FALL_TERM                        VARCHAR2(20 BYTE),
  HR_GENDER_DESCRIPTION                VARCHAR2(30 BYTE),
  HR_CURRENT_ID                        VARCHAR2(10 BYTE),
  IPEDS_RACE_ETHNICITY_DESCRIPTION     VARCHAR2(50 BYTE),
  OIR_FACULTY_STAFF_DETAILED_ROLE      VARCHAR2(50 BYTE),
  OIR_MGMT_FAC_AND_INSTR_IN_FALL_TERM  VARCHAR2(50 BYTE),
  OIR_OTHER_DEGREE_NOTES               VARCHAR2(100 BYTE),
  OIR_DOCTORATE                        VARCHAR2(20 BYTE),
  OIR_HIGHEST_DEGREE_CATEGORY          VARCHAR2(25 BYTE),
  OIR_TERMINAL_DEGREE                  VARCHAR2(50 BYTE),
  OIR_PRIMARY_ROLE                     VARCHAR2(25 BYTE),
  HR_PIDM                              INTEGER,
  OIR_POSITION                         VARCHAR2(25 BYTE),
  OIR_FACULTY_SALARY                   NUMBER(13,3),
  OIR_SCHOOL                           VARCHAR2(50 BYTE),
  OIR_TENURE_STATUS                    VARCHAR2(50 BYTE),
  PERSON_SKEY                          INTEGER,
  CREATED_DATE                         DATE,
  MODIFIED_DATE                        DATE,
  OIR_IPEDS_OCCUPATION_CATEGORY        VARCHAR2(1000 BYTE),
  IPEDS_RANK_DESCRIPTION               VARCHAR2(200 BYTE),
  OIR_POSN_SEQUENCE                    INTEGER
);

CREATE TABLE VPAA_PPAGGENL_FIS --Degree Table
(
  PIDM                                           INTEGER NOT NULL,
  VPAA_FACULTY_INSTITUTION_CODE                  VARCHAR2(6 CHAR) NOT NULL,
  VPAA_FACULTY_INSTITUTION_NAME                  VARCHAR2(30 CHAR),
  VPAA_FACULTY_OFFICIAL_TRANSCRIPT               VARCHAR2(1 CHAR),
  VPAA_FACULTY_OFFICIAL_TRANSCRIPT_RECEIPT_DATE  DATE,
  VPAA_FACULTY_DEGREE_CODE                       VARCHAR2(6 CHAR),
  VPAA_FACULTY_DEGREE_DESCRIPTION                VARCHAR2(30 CHAR),
  VPAA_FACULTY_GRADUATION_DATE                   DATE,
  VPAA_FACULTY_TERMINAL_DEGREE                   VARCHAR2(1 CHAR),
  VPAA_FACULTY_SUBJECT_CODE                      VARCHAR2(4 CHAR),
  VPAA_FACULTY_SUBJECT_DESCRIPTION               VARCHAR2(30 CHAR),
  VPAA_FACULTY_CIPC_CODE                         VARCHAR2(6 CHAR),
  VPAA_FACULTY_ACAT_CODE                         VARCHAR2(2 CHAR),
  CREATED_DATE                                   DATE,
  MODIFIED_DATE                                  DATE,
  PERSON_SKEY                                    INTEGER,
  VPAA_FACULTY_YEARS_SINCE_GRADUATION            NUMBER,
  VPAA_CURRENT_DEGREE                            INTEGER
);

--Crosswalk tables
CREATE TABLE WHOIRMGR.SUBJ_CODE_CROSSWALK
(
  VPAA_FACULTY_DEGREE_CODE         VARCHAR2(20 BYTE),
  VPAA_FACULTY_DEGREE_DESCRIPTION  VARCHAR2(50 BYTE),
  VPAA_FACULTY_SUBJECT_CODE        VARCHAR2(20 BYTE),
  SUBJECT_CODE_DESCRIPTION         VARCHAR2(50 BYTE),
  PERFORMANCE_OR_THEORY            VARCHAR2(20 BYTE),
  OIR_TERMINAL_DEGREE_STATUS       VARCHAR2(30 BYTE),
  CREATED_DATE                     DATE,
  MODIFIED_DATE                    DATE
);

CREATE TABLE WHOIRMGR.DIM_DEGREE_CROSSWALK
(
  OIR_DEGREE_LEVEL                       VARCHAR2(50 BYTE),
  VPAA_DEGREE_CODE                       VARCHAR2(25 BYTE),
  VPAA_DEGREE_DESCRIPTION                VARCHAR2(100 BYTE),
  CREATED_DATE                           DATE,
  MODIFIED_DATE                          DATE,
  DEGREE_LEVEL_SKEY                      INTEGER,
  OIR_DEGREE_HIERARCHY                   INTEGER,
  OIR_TERMINAL_DEGREE_STATUS             VARCHAR2(50 BYTE),
  VPAA_FACULTY_SUBJECT_CODE              VARCHAR2(25 BYTE),
  VPAA_FACULTY_SUBJECT_CODE_DESCRIPTION  VARCHAR2(100 BYTE),
  PERFORMANCE_OR_THEORY                  VARCHAR2(25 BYTE)
);

CREATE TABLE WHOIRMGR.CLIC_INSTRUCTORS_CROSSWALK
(
  HR_DEPARTMENT_HOME_ORGN_CODE              NVARCHAR2(20),
  HR_DEPARTMENT_HOME_ORGN_CODE_DESCRIPTION  NVARCHAR2(50),
  VPAA_FACULTY_DEGREE_CODE                  VARCHAR2(20 BYTE),
  VPAA_FACULTY_DEGREE_DESCRIPTION           VARCHAR2(50 BYTE),
  VPAA_FACULTY_SUBJECT_CODE                 VARCHAR2(20 BYTE),
  SUBJECT_CODE_DESCRIPTION                  VARCHAR2(50 BYTE),
  OIR_TERMINAL_DEGREE_STATUS                VARCHAR2(40 BYTE)
);

更新：这是什么结束工作！！在子查询中，我必须执行左联接，而不是where id在两个表中都匹配。

UPDATE SNP_FACULTY_CENSUS SFC
SET OIR_TERMINAL_DEGREE = (
WITH HIGHES_DEG_VALUE AS (
    SELECT max(D.OIR_DEGREE_HIERARCHY) AS MAX_DEG
    FROM VPAA_PPAGGENL_FIS  V
    JOIN DIM_DEGREE_CROSSWALK D
        ON V.VPAA_FACULTY_DEGREE_CODE =
           D.VPAA_DEGREE_CODE 
    left JOIN SNP_FACULTY_CENSUS S
            ON V.PERSON_SKEY = S.PERSON_sKEY) --CHANGE TO  = SRC.PERSON_SKEY
SELECT 
CASE 
    WHEN MAX_DEG = 30 
        THEN  --CHECK IF EXIST ON CROSSWALKS AND USE THAT VALUE IF IT DOES
        (CASE
            WHEN EXISTS (SELECT V.VPAA_FACULTY_DEGREE_CODE, --IF ON SUBJ CODE CROSSWALK
                         V.VPAA_FACULTY_SUBJECT_CODE
                         FROM VPAA_PPAGGENL_FIS V
                         JOIN DIM_DEGREE_CROSSWALK D
                         ON V.VPAA_FACULTY_DEGREE_CODE = D.VPAA_DEGREE_CODE 
                         INNER JOIN  SUBJ_CODE_CROSSWALK  S
                         ON V.VPAA_FACULTY_DEGREE_CODE = S.VPAA_FACULTY_DEGREE_CODE
                            and S.VPAA_FACULTY_SUBJECT_CODE = V.VPAA_FACULTY_SUBJECT_CODE 
                            left JOIN SNP_FACULTY_CENSUS S
                            ON V.PERSON_SKEY = S.PERSON_sKEY
                            --WHERE V.PERSON_SKEY = SFC.PERSON_SKEY ----CHANGE TO  = SRC.PERSON_SKEY
                         )
                THEN (SELECT DISTINCT  -- USE TERMINAL VALUE FROM SUBJ CODE
                       CASE
                             WHEN s.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN s.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END
                      FROM SUBJ_CODE_CROSSWALK  S
                                   INNER JOIN VPAA_PPAGGENL_FIS V
                                       ON V.VPAA_FACULTY_DEGREE_CODE =
                                          S.VPAA_FACULTY_DEGREE_CODE
                                    AND S.VPAA_FACULTY_SUBJECT_CODE =
                                          V.VPAA_FACULTY_SUBJECT_CODE
                                          left JOIN SNP_FACULTY_CENSUS S
                                        ON V.PERSON_SKEY = S.PERSON_sKEY)
                                    --WHERE  V.PERSON_SKEY = SFC.PERSON_SKEY )----CHANGE TO  = SRC.PERSON_SKEY )                        

            WHEN EXISTS (SELECT DISTINCT V.VPAA_FACULTY_DEGREE_CODE, --IF ON CLIC INSTRUCTORS CROSSWALK
                                V.VPAA_FACULTY_SUBJECT_CODE,
                                I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG
                         FROM VPAA_PPAGGENL_FIS V
                         INNER JOIN CLIC_INSTRUCTORS_CROSSWALK C
                         ON V.VPAA_FACULTY_DEGREE_CODE = C.VPAA_FACULTY_DEGREE_CODE 
                         AND V.VPAA_FACULTY_SUBJECT_CODE = C.VPAA_FACULTY_SUBJECT_CODE
                         INNER JOIN IPEDS_REPORT_DETAILS I ON V.PIDM = I.HR_PIDM
                         --WHERE V.PERSON_SKEY = SFC.PERSON_SKEY ----CHANGE TO  = SRC.PERSON_SKEY
                         left JOIN SNP_FACULTY_CENSUS S
                            ON V.PERSON_SKEY = S.PERSON_sKEY
                         AND  (I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396000'
                              OR I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396010')
                         ) 
                THEN (SELECT DISTINCT 
                        CASE
                             WHEN C.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN C.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END  
                       FROM CLIC_INSTRUCTORS_CROSSWALK C
                       INNER JOIN VPAA_PPAGGENL_FIS V
                           ON C.VPAA_FACULTY_DEGREE_CODE =
                              V.VPAA_FACULTY_DEGREE_CODE
                        AND C.VPAA_FACULTY_SUBJECT_CODE =
                              V.VPAA_FACULTY_SUBJECT_CODE
                        --WHERE V.PERSON_SKEY = SFC.PERSON_SKEY)----CHANGE TO  = SRC.PERSON_SKEY)
                        left JOIN SNP_FACULTY_CENSUS S
                        ON V.PERSON_SKEY = S.PERSON_sKEY)
       ELSE --USE DIM DEGREE CROSSWALK NORMALLY
            (SELECT DISTINCT 
                        CASE
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END  
            FROM DIM_DEGREE_CROSSWALK D
                 JOIN VPAA_PPAGGENL_FIS V
                     ON V.VPAA_FACULTY_DEGREE_CODE =
                        D.VPAA_DEGREE_CODE
               --WHERE V.PERSON_SKEY = SFC.PERSON_SKEY ----CHANGE TO  = SRC.PERSON_SKEY)
            left JOIN SNP_FACULTY_CENSUS S
            ON V.PERSON_SKEY = S.PERSON_sKEY
               ORDER BY OIR_DEGREE_HIERARCHY DESC
               FETCH FIRST ROW ONLY )
        END) --USE DIM DEGREE CROSSWALK FOR THE TERMINAL VALUE FOR THE MAX DEG THERE

    WHEN MAX_DEG != 30
    THEN
        (SELECT DISTINCT 
                        CASE
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END  
            FROM DIM_DEGREE_CROSSWALK D
             JOIN VPAA_PPAGGENL_FIS V
                 ON V.VPAA_FACULTY_DEGREE_CODE =
                    D.VPAA_DEGREE_CODE
             left JOIN SNP_FACULTY_CENSUS S
            ON V.PERSON_SKEY = S.PERSON_sKEY
           --WHERE V.PERSON_SKEY = SFC.PERSON_SKEY ----CHANGE TO  = SRC.PERSON_SKEY)
           ORDER BY OIR_DEGREE_HIERARCHY DESC
           FETCH FIRST ROW ONLY )
    ELSE NULL
END 
FROM HIGHES_DEG_VALUE ) 
WHERE SFC.OIR_FALL_TERM = 'Fall 2019'

与update一起使用时，需要对update表使用alias。如果没有，它就不起作用。
另外，您不能在with子句中调用更新的表，此时该表是未知的，这就是您获得无效标识符的原因。要解决这个问题，您需要删除该条件并在末尾添加。
试试这个，但是我写的时候还不知道数据模型，所以请注意。

UPDATE SNP_FACULTY_CENSUS SFC
SET OIR_TERMINAL_DEGREE = (
  WITH HIGHES_DEG_VALUE AS (
    SELECT max(D.OIR_DEGREE_HIERARCHY) AS MAX_DEG
    FROM VPAA_PPAGGENL_FIS  V
    JOIN DIM_DEGREE_CROSSWALK D
        ON V.VPAA_FACULTY_DEGREE_CODE = D.VPAA_DEGREE_CODE  
    LEFT JOIN SNP_FACULTY_CENSUS S 
        ON V.PERSON_SKEY = S.PERSON_SKEY
     ) 
SELECT 
CASE 
    WHEN MAX_DEG = 30 
        THEN  --CHECK IF EXIST ON CROSSWALKS AND USE THAT VALUE IF IT DOES
        (CASE
            WHEN EXISTS (SELECT V.VPAA_FACULTY_DEGREE_CODE, --IF ON SUBJ CODE CROSSWALK
                         V.VPAA_FACULTY_SUBJECT_CODE
                         FROM VPAA_PPAGGENL_FIS V
                         JOIN DIM_DEGREE_CROSSWALK D
                         ON V.VPAA_FACULTY_DEGREE_CODE = D.VPAA_DEGREE_CODE 
                         INNER JOIN  SUBJ_CODE_CROSSWALK  S
                         ON V.VPAA_FACULTY_DEGREE_CODE = S.VPAA_FACULTY_DEGREE_CODE
                            and S.VPAA_FACULTY_SUBJECT_CODE = V.VPAA_FACULTY_SUBJECT_CODE 
                            WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ----CHANGE TO  = SRC.PERSON_SKEY
                         )
                THEN (SELECT DISTINCT  -- USE TERMINAL VALUE FROM SUBJ CODE
                       CASE
                             WHEN s.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN s.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END
                      FROM SUBJ_CODE_CROSSWALK  S
                                   INNER JOIN VPAA_PPAGGENL_FIS V
                                       ON V.VPAA_FACULTY_DEGREE_CODE =
                                          S.VPAA_FACULTY_DEGREE_CODE
                                    AND S.VPAA_FACULTY_SUBJECT_CODE =
                                          V.VPAA_FACULTY_SUBJECT_CODE
                                    WHERE  V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY )----CHANGE TO  = SRC.PERSON_SKEY )                        

            WHEN EXISTS (SELECT DISTINCT V.VPAA_FACULTY_DEGREE_CODE, --IF ON CLIC INSTRUCTORS CROSSWALK
                                V.VPAA_FACULTY_SUBJECT_CODE,
                                I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG
                         FROM VPAA_PPAGGENL_FIS V
                         INNER JOIN CLIC_INSTRUCTORS_CROSSWALK C
                         ON V.VPAA_FACULTY_DEGREE_CODE = C.VPAA_FACULTY_DEGREE_CODE 
                         AND V.VPAA_FACULTY_SUBJECT_CODE = C.VPAA_FACULTY_SUBJECT_CODE
                         INNER JOIN IPEDS_REPORT_DETAILS I ON V.PIDM = I.HR_PIDM
                         WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ----CHANGE TO  = SRC.PERSON_SKEY
                         AND  (I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396000'
                              OR I.OIR_EMPLOYEE_DEPARTMENT_HOME_ORG = '396010')
                         ) 
                THEN (SELECT DISTINCT 
                        CASE
                             WHEN C.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN C.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END  
                       FROM CLIC_INSTRUCTORS_CROSSWALK C
                       INNER JOIN VPAA_PPAGGENL_FIS V
                           ON C.VPAA_FACULTY_DEGREE_CODE =
                              V.VPAA_FACULTY_DEGREE_CODE
                        AND C.VPAA_FACULTY_SUBJECT_CODE =
                              V.VPAA_FACULTY_SUBJECT_CODE
                        WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY )----CHANGE TO  = SRC.PERSON_SKEY)
       ELSE --USE DIM DEGREE CROSSWALK NORMALLY
            (SELECT DISTINCT 
                        CASE
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END  
            FROM DIM_DEGREE_CROSSWALK D
                 JOIN VPAA_PPAGGENL_FIS V
                     ON V.VPAA_FACULTY_DEGREE_CODE =
                        D.VPAA_DEGREE_CODE
               WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ----CHANGE TO  = SRC.PERSON_SKEY)
               ORDER BY OIR_DEGREE_HIERARCHY DESC
               FETCH FIRST ROW ONLY )
        END) --USE DIM DEGREE CROSSWALK FOR THE TERMINAL VALUE FOR THE MAX DEG THERE

    WHEN MAX_DEG != 30
    THEN
        (SELECT DISTINCT 
                        CASE
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Terminal'
                             THEN
                                 'Yes'
                             WHEN D.OIR_TERMINAL_DEGREE_STATUS =
                                  'Not terminal'
                             THEN
                                 'No'
                             ELSE
                                 'Unknown'
                        END  
            FROM DIM_DEGREE_CROSSWALK D
             JOIN VPAA_PPAGGENL_FIS V
                 ON V.VPAA_FACULTY_DEGREE_CODE =
                    D.VPAA_DEGREE_CODE
           WHERE V.PERSON_SKEY = SNP_FACULTY_CENSUS.PERSON_SKEY ----CHANGE TO  = SRC.PERSON_SKEY)
           ORDER BY OIR_DEGREE_HIERARCHY DESC
           FETCH FIRST ROW ONLY )
    ELSE NULL
END 
FROM HIGHES_DEG_VALUE
) WHERE SNP_FACULTY_CENSUS.OIR_FALL_TERM = 'Fall 2019' ;

2条答案

按热度按时间

djmepvbi1#

赞(0）回复(0）举报 2021-07-27

qv7cva1a2#

一 MERGE 在这种情况下效果最好。
一个好主意 MERGE 是从源表/查询（在 USING 部分 MERGE 语句），并将其合并到目标表（在 MERGE INTO 部分 MERGE 声明）。
源数据集中的哪一行指向目标表中由在合并条件（后面的条件）上指定的行 ON 在 MERGE 声明）。
既然您已经设置了源数据集和目标数据集，并指定了如何进行合并，那么您就必须指定合并完成后会发生什么。源数据集中的行可能在目标表中找到目标，也可能没有。如果是这样的话，你可以指定 WHEN MATCHED 条款。如果它找不到目标，你就指定在 WHEN NOT MATCHED 条款。
记住这些，你的 MERGE 变成

MERGE INTO snp_faculty_census sfc -- DESTINATION table
USING (SELECT v.person_skey,
              MAX (d.oir_degree_hierarchy) AS max_deg
       FROM vpaa_ppaggenl_fis v
            JOIN dim_degree_crosswalk d
                ON v.vpaa_faculty_degree_code = d.vpaa_degree_code) md -- SOURCE DATASET
ON (sfc.person_skey = md.person_skey) -- MERGEing condition
WHEN MATCHED THEN
    UPDATE SET sfc.oir_terminal_degree = md.max_deg; -- WHAT HAPPENS WHEN MEGRED

在我看来，这就是你需要做的。您可以在源数据集中添加一个groupby，以防止同一行被一次又一次地合并，但是如果不这样做，它应该可以工作。

update table set column equal to select where id matches outside of oracle中的查询

2条答案

相关问题

热门标签

最新问答