Oracle error : ORA- 00905 : Missing keyword-CodePudding

I'm executing this SQL statement:

MERGE INTO INTERVIEW OLD_R
USING NEW_INTERVIEW NEW_R
ON (NEW_R.REG_NO = OLD_R.REG_NO)
WHEN MATCHED
    AND (NEW_R.NAME <> OLD_R.NAME OR NEW_R.MOB_NO <> OLD_R.MOB_NO)
THEN UPDATE SET 
    OLD_R.NAME = NEW_R.NAME,
    OLD_R.MOB_NO = NEW_R.MOB_NO
WHEN NOT MATCHED BY OLD_R 
THEN 
     INSERT(OLD_R.NAME, OLD_R.REG_NO, OLD_R.MOB_NO)
     VALUES(NEW_R.NAME, NEW_R.REG_NO, NEW_R.MOB_NO)
WHEN NOT MATCHED BY NEW_R
THEN DELETE;

against an Oracle database to back up a table called assignment, but I get this error:

ORA-00905: Missing keyword

CodePudding user response：

Wrong syntax. Should've been

MERGE INTO INTERVIEW OLD_R
     USING NEW_INTERVIEW NEW_R
        ON (NEW_R.REG_NO = OLD_R.REG_NO)
WHEN MATCHED
THEN
   UPDATE SET OLD_R.NAME = NEW_R.NAME, OLD_R.MOB_NO = NEW_R.MOB_NO
           WHERE    NEW_R.NAME <> OLD_R.NAME
                 OR NEW_R.MOB_NO <> OLD_R.MOB_NO
WHEN NOT MATCHED
THEN
   INSERT     (OLD_R.NAME, OLD_R.REG_NO, OLD_R.MOB_NO)
       VALUES (NEW_R.NAME, NEW_R.REG_NO, NEW_R.MOB_NO);

CodePudding user response：

WHEN MATCHED
   AND (NEW_R.NAME <> OLD_R.NAME OR NEW_R.MOB_NO <> OLD_R.MOB_NO)
THEN UPDATE SET 
    OLD_R.NAME = NEW_R.NAME,
    OLD_R.MOB_NO = NEW_R.MOB_NO

Should be

WHEN MATCHED THEN
  UPDATE
  SET   OLD_R.NAME = NEW_R.NAME,
        OLD_R.MOB_NO = NEW_R.MOB_NO
  WHERE NEW_R.NAME <> OLD_R.NAME OR NEW_R.MOB_NO <> OLD_R.MOB_NO

Then

WHEN NOT MATCHED BY OLD_R

Should just be:

WHEN NOT MATCHED

and:

WHEN NOT MATCHED BY NEW_R
THEN DELETE;

Is not a syntax that is supported by Oracle; you will need to delete those rows using a separate DELETE statement.