Update statement oracle - ORA-00933: SQL command not properly ended

Question

Having a bit of trouble running this update command. I keep getting the error message "ORA-00933: SQL command not properly ended". Can anyone assist in helping me get this correct? Here is what I have at the moment?

Thanks, George

UPDATE    A
SET             EARLIEST_STARTDATE =  CASE WHEN (DATE_SUBMITTED <     TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY'))
                                                                            THEN TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY')
                                                                            ELSE TO_DATE(DATE_SUBMITTED,'DD/MM/YY') END 
FROM        TABLE1 A
                 INNER JOIN TABLE2 C on A.ID = C.ID                                      
WHERE     (EARLIEST_STARTDATE IS NULL) AND 
                (A.WS_ELIGIBILITY_STATUS = 1) AND 
                (A.CLAIM_ADD_CRITERIA_FLAG = 'N' OR A.CAF = 'Y' or A.CCA_CODE = 'SC' AND A.CLAIM_ADD_CRITERIA_FLAG = 'Y') AND 
                (ADD_MONTHS(C.DOB, 24) BETWEEN TO_DATE('01/01/' || EXTRACT(YEAR FROM ADD_MONTHS(C.DOB, 24)),'DD/MM/YYYY') AND TO_DATE('31/03/' || EXTRACT(YEAR FROM ADD_MONTHS(C.DOB, 24)),'DD/MM/YYYY'))

Does Oracle support the non-standard SQL syntax UPDATE FROM? — jarlh, Nov 04 '15 at 10:27
Oracle does not support UPDATE FROM. http://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table?rq=1 http://stackoverflow.com/questions/14632621/sql-command-not-properly-ended-error-from-update-query?lq=1 — Thilo, Nov 04 '15 at 10:28

score 0 · Answer 1 · answered Nov 04 '15 at 10:31

0

UPDATE    A
SET             EARLIEST_STARTDATE =  
(SELECT 
CASE WHEN (DATE_SUBMITTED <     TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY'))
                                                                            THEN TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY')
                                                                            ELSE TO_DATE(DATE_SUBMITTED,'DD/MM/YY') END 
FROM        TABLE1 A
                 INNER JOIN TABLE2 C on A.ID = C.ID                                      
WHERE     (EARLIEST_STARTDATE IS NULL) AND 
                (A.WS_ELIGIBILITY_STATUS = 1) AND 
                (A.CLAIM_ADD_CRITERIA_FLAG = 'N' OR A.CAF = 'Y' or A.CCA_CODE = 'SC' AND A.CLAIM_ADD_CRITERIA_FLAG = 'Y') AND 
                (ADD_MONTHS(C.DOB, 24) BETWEEN TO_DATE('01/01/' || EXTRACT(YEAR FROM ADD_MONTHS(C.DOB, 24)),'DD/MM/YYYY') AND TO_DATE('31/03/' || EXTRACT(YEAR FROM ADD_MONTHS(C.DOB, 24)),'DD/MM/YYYY')))

answered Nov 04 '15 at 10:31

anwaar_hell

756
5
23

Tried this... But now getting "ORA-01427: single-row subquery returns more than one row" – George Georgiou Nov 04 '15 at 10:34
You need to correlate the subquery with the row being updated; probably o n the `a.cid` column if that's a PK. Some explanation of what you're changing and why would be nice though, rather than just a code dump. – Alex Poole Nov 04 '15 at 10:38
@GeorgeGeorgiou then in Subquery table2 there are multiple ids correspoding to id in table A . please add more join condition so that subquery will return only single row per id. – anwaar_hell Nov 04 '15 at 10:40
Oracle does not support `FROM` or `JOIN` in an update statement – Nov 04 '15 at 10:55

score 0 · Accepted Answer · answered Nov 04 '15 at 10:52

Oracle doesn't let you join within an update statement. You need to use a correlated subquery, e.g.:

UPDATE TABLE1 TOP_A
SET EARLIEST_STARTDATE = (
  SELECT CASE WHEN DATE_SUBMITTED <
      TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)), 'DD/MM/YYYY')
    THEN TO_DATE('01/04/' || EXTRACT(YEAR FROM ADD_MONTHS(DOB, 24)),'DD/MM/YYYY')
    ELSE TO_DATE(DATE_SUBMITTED,'DD/MM/YY') END
  FROM TABLE1 SUB_A
  INNER JOIN TABLE2 SUB_C on SUB_A.ID = SUB_C.ID
  WHERE SUB_A.ID = TOP_A.ID
  AND ADD_MONTHS(SUB_C.DOB, 24) BETWEEN
    TO_DATE('01/01/' || EXTRACT(YEAR FROM ADD_MONTHS(SUB_C.DOB, 24)),'DD/MM/YYYY')
      AND TO_DATE('31/03/' || EXTRACT(YEAR FROM ADD_MONTHS(SUB_C.DOB, 24)),'DD/MM/YYYY')
)
WHERE EARLIEST_STARTDATE IS NULL
AND WS_ELIGIBILITY_STATUS = 1
AND (CLAIM_ADD_CRITERIA_FLAG = 'N' OR CAF = 'Y' OR (CCA_CODE = 'SC' AND CLAIM_ADD_CRITERIA_FLAG = 'Y'))
/

This assumes that TABLE_1.ID is unique. The subquery does the join between the two tables, and it's correlated with the row being updated by the WHERE SUB_A.ID = TOP_A.ID clause inside the subquery.

Your AND/OR section is a bit unclear so you may need to rethink the parentheses around those clauses to get the correct boolean result for your business needs.

The TO_DATE(DATE_SUBMITTED,'DD/MM/YY') in the case else looks suspicious too; if DATE_SUBMITTED is already a date then just refer to that directly, don't call to_date() for it. You're doing an implicit conversion to a string before converting back; and you're probably going to lose the century from the date in the process because of the YY model.

The other date handling could be simplified too.

Update statement oracle - ORA-00933: SQL command not properly ended

2 Answers2