What's wrong with this UPDATE SQL JOIN syntax?

Question

Trying to update a column in a table based on the value in another table as part of a data migration. All the columns exist in both tables in the join, but am receiving an unexpected error message when I run the following:

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;

I receive the error:

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:

I'm sure it's got to be a syntax issue, but can't see where. Any ideas?

EDIT - thanks, solution obtained. For learning purposes (I've done T-SQL for the last 20 years and only just learning PL-SQL), I also re-wrote the query as a MERGE INTO - but this also failed. Any obvious issues here?:

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;

How has your Merge statement "failed"? Wrong results, an error, something else? — Boneist, Aug 06 '19 at 09:41
It failed earlier, but I just re-ran it and it worked fine. Strange. Not an issue after all - thanks for looking though! — AdzzzUK, Aug 06 '19 at 09:49

score 1 · Accepted Answer · answered Aug 06 '19 at 08:04

Should be

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);

What's wrong with this UPDATE SQL JOIN syntax?

1 Answers1