oracle - 这个 UPDATE SQL JOIN 语法有什么问题？

Question

作为数据迁移的一部分，尝试根据另一个表中的值更新表中的列。所有列都存在于连接中的两个表中，但是当我运行以下命令时收到意外错误消息：

UPDATE CSB 
SET  IFS10_SCHEDULE = CS.SCHEDULE_NO
FROM IC_U_CUSTOMER_SCHED_B CSB
JOIN CUST_SCHED CS
       ON     CSB.CUSTOMER_NO = CS.CUSTOMER_NO
          AND CS.SHIP_ADDR_NO = CSB.SHIP_ADDR_NO
          AND CSB.CUSTOMER_PART_NO = CS.CUSTOMER_PART_NO
          AND CSB.DOC_NO = CS.DOC_NO
          AND CSB.AGREEMENT_ID = CS.AGREEMENT_ID
          AND CSB.CUST_SCHEDULE_TYPE = CS.CUST_SCHEDULE_TYPE;

我收到错误：

Error at Command Line : 3 Column : 1
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

我确定这一定是语法问题，但看不到在哪里。有任何想法吗？

编辑 - 谢谢，已获得解决方案。出于学习目的（我在过去 20 年中完成了 T-SQL，并且只学习了 PL-SQL），我还将查询重写为 MERGE INTO ——但这也失败了。这里有什么明显的问题吗？：

MERGE INTO IC_U_CUSTOMER_SCHED_B CSB
USING CUST_SCHED CS
ON (CSB.CUSTOMER_NO = CS.CUSTOMER_NO
          AND CS.SHIP_ADDR_NO = CSB.SHIP_ADDR_NO
          AND CSB.CUSTOMER_PART_NO = CS.CUSTOMER_PART_NO
          AND CSB.DOC_NO = CS.DOC_NO
          AND CSB.AGREEMENT_ID = CS.AGREEMENT_ID
          AND CSB.CUST_SCHEDULE_TYPE = CS.CUST_SCHEDULE_TYPE)
WHEN MATCHED THEN UPDATE SET CSB.IFS10_SCHEDULE = CS.SCHEDULE_NO
WHERE CSB.IFS10_SCHEDULE != CS.SCHEDULE_NO;

标签： oracleplsqloracle-sqldeveloper

Answer 1

应该

UPDATE ic_u_customer_sched_b csb
   SET csb.ifs10_schedule =
          (SELECT cs.schedule_no
             FROM cust_sched cs
            WHERE     csb.customer_no = cs.customer_no
                  AND cs.ship_addr_no = csb.ship_addr_no
                  AND csb.customer_part_no = cs.customer_part_no
                  AND csb.doc_no = cs.doc_no
                  AND csb.agreement_id = cs.agreement_id
                  AND csb.cust_schedule_type = cs.cust_schedule_type);