SQL Error: ORA-00905: missing keyword, can someone please tel me whats wrong with the below merge statement

Question

MERGE INTO table_a PARTITION (x) A
USING (SELECT distinct col1,col2 FROM table_b)b
ON (A.col1=B.col1(+) AND A.col2=B.col2(+))
WHEN MATCHED THEN
UPDATE SET col3='Y'
WHEN NOT MATCHED THEN
UPDATE SET col3='N'; 

Answer 1

The WHEN NOT MATCHED clause cannot perform an UPDATE - what rows is it supposed to update if it didn't find any?

If you want to update A depending on whether there's an existing row in B, one approach is to pull A into the merge query (this assumes your table A has a primary key PK):

MERGE INTO table_a PARTITION (x) tgt
  USING (
    SELECT A.PK, B.col1, B.col2 
      FROM table_a A
      LEFT JOIN table_b b ON A.col1 = B.col1 AND A.col2 = B.col2) src
ON (src.pk = tgt.pk)
WHEN MATCHED THEN
  UPDATE SET col3 = (CASE WHEN src.col1 IS NOT NULL THEN 'Y' ELSE 'N' END);