Anyone know why this SQL update statement isn't working?

Question

Can anyone spot why the below SQL statement fails with 'ORA-00933:SQL command not properly ended'?

update member_care_ben mcb
set mcb.date_to = me.date_left
from member_employment me
inner join mcb
  on me.client_ref=mcb.client_ref
  and me.member_ref=mcb.member_ref
  and me.employment_ref=mcb.employment_ref
inner join member_basic_detail mbd
  on me.member_ref=mbd.member_ref
where me.status = '4'
  and me.date_left > to_date('01/04/2014', 'DD/MM/YYYY')
  and mcb.date_to > me.date_left

Answer 1

That's invalid syntax for Oracle. Try merge:

MERGE INTO member_care_ben mcb
     USING (SELECT me.client_ref,
                   me.member_ref,
                   me.employment_ref,
                   me.date_left
              FROM member_emplyment me
                   JOIN member_basic_detail mbd
                      ON me.member_ref = mbd.member_ref
             WHERE     me.status = '4'
                   AND me.date_left > DATE '2014-04-01') x
        ON (    x.client_ref = mcb.client_ref
            AND x.member_ref = mcb.member_ref
            AND x.employment_ref = mcb.employment_ref)
WHEN MATCHED
THEN
   UPDATE SET mcb.date_to = x.date_left
           WHERE mcb.date_to > x.date_left;