sql - 当涉及两个以上的表时更新 Oracle 的 SQL - ORA-00933

Question

我对编写 SQL 比较陌生。我写了以下内容：

UPDATE PS_EMAIL_ADDRESSES C
SET C.EMAIL_ADDR = B.SFA_CR_EMAIL_ADDR
 FROM PS_LOAN_ORIGNATN A, PS_SFA_COD_BORROWR B
 WHERE A.EMPLID <> A.BORR_EMPLID
    AND A.AID_YEAR = '2019'
    AND A.ACAD_CAREER = 'UGRD'
    AND A.BORR_EMPLID <> ' '
    AND B.SFA_CR_AWARD_YEAR = A.AID_YEAR
    AND B.SFA_CR_ID_SSN = A.BORR_SSN
    AND B.SFA_CR_EMAIL_ADDR <> ' '
    AND B.SFA_CR_DOCUMENT_ID = (SELECT MAX( D.SFA_CR_DOCUMENT_ID)
 FROM PS_SFA_COD_BORROWR D
 WHERE D.SFA_CR_AWARD_YEAR = B.SFA_CR_AWARD_YEAR
    AND D.SFA_CR_ID_SSN = B.SFA_CR_ID_SSN
    AND D.SFA_CR_EMAIL_ADDR <> ' ')
    AND EXISTS (SELECT 1
 FROM PS_EMAIL_ADDRESSES C
 WHERE C.EMPLID = A.BORR_EMPLID
    AND C.E_ADDR_TYPE = 'PEM'
    AND C.EMAIL_ADDR <> B.SFA_CR_EMAIL_ADDR)

但是在 SQL Fiddle 上检查它，我得到了 ORA-00933。我可能问得不对，但我一直在研究不同的版本。

标签： sqloracleoracle11g

Answer 1

一些问题：那不是 Oracle Sql 语法，你已经重用了别名 C，而且你的缩进有点难以理解。

看看这是否适合你：

UPDATE PS_EMAIL_ADDRESSES E
   SET E.EMAIL_ADDR = (SELECT B.SFA_CR_EMAIL_ADDR
  FROM PS_LOAN_ORIGNATN A, PS_SFA_COD_BORROWR B
 WHERE A.EMPLID <> A.BORR_EMPLID
   AND A.AID_YEAR = '2019'
   AND A.ACAD_CAREER = 'UGRD'
   AND A.BORR_EMPLID <> ' '
   AND B.SFA_CR_AWARD_YEAR = A.AID_YEAR
   AND B.SFA_CR_ID_SSN = A.BORR_SSN
   AND B.SFA_CR_EMAIL_ADDR <> ' '
   AND B.SFA_CR_DOCUMENT_ID = (SELECT MAX( D.SFA_CR_DOCUMENT_ID)
                                 FROM PS_SFA_COD_BORROWR D
                                WHERE D.SFA_CR_AWARD_YEAR = B.SFA_CR_AWARD_YEAR
                                  AND D.SFA_CR_ID_SSN = B.SFA_CR_ID_SSN
                                  AND D.SFA_CR_EMAIL_ADDR <> ' ')
   AND EXISTS (SELECT 1
                 FROM PS_EMAIL_ADDRESSES C
                WHERE C.EMPLID = A.BORR_EMPLID
                  AND C.E_ADDR_TYPE = 'PEM'
                  AND C.EMAIL_ADDR <> B.SFA_CR_EMAIL_ADDR)
);