update statement failing in oracle - sql command not properly ended

Question

I am writing update select in oracle and getting error SQL command not properly ended. I am basically updating the investment_status_id field in managerstrategy table based on conditon. If I run only select statement it runs fine. Whats wrong with my update statement

UPDATE  mgr.managerstrategy
SET     ms.investment_status_id = mas.investment_status_id
FROM    mgr.managerstrategy  ms  inner join mgr.V_MANAGERSTRATEGY_AUM_SUMMARY  mas
ON      mas.MANAGERSTRATEGY_ID = ms.ID 
WHERE   mas.is_invested = 1

select statement

select * from  mgr.managerstrategy ms  inner join mgr.V_MANAGERSTRATEGY_AUM_SUMMARY mas
ON      mas.MANAGERSTRATEGY_ID = ms.ID 
WHERE   mas.is_invested = 1

Oracle does not support ANSI 92 explicit join syntax for UPDATE (or DELETE). I agree it would be highly nest if it did but alas... Anyway, the linked question above provides the workarounds open to us. — APC, Mar 19 '19 at 12:36

score 1 · Answer 1 · answered Mar 19 '19 at 12:34

This is invalid syntax in Oracle. The UPDATE statement does not allow a FROM clause.

You may be able to use a subquery, something like this:

UPDATE  mgr.managerstrategy ms
SET     ms.investment_status_id = (
  SELECT mas.investment_status_id
  FROM   mgr.V_MANAGERSTRATEGY_AUM_SUMMARY  mas
  WHERE mas.MANAGERSTRATEGY_ID = ms.ID 
    AND mas.is_invested = 1
)

Alternatively, look at the MERGE statement.

update statement failing in oracle - sql command not properly ended

1 Answers1