SQL ORACLE ORA-00969: missing ON keyword?

Question

I have two different tables : Table_a and Table_b, both have same columns PartNo and Material_Desc. I want the Material_Desc in Table_b to update the Material_Desc in Table_a when PartNo are equals. This is my query

MERGE INTO Table_b 
USING ( SELECT t1.rowid AS rid
               , t2.Material_Desc
        FROM Table_b t1 
           JOIN Table_a t2 
           ON Table_b.PartNo = Table_a.PartNo ) 
ON rowid = rid 
WHEN MATCHED THEN 
    UPDATE 
    SET Table_a.Material_Desc = Table_b.Material_Desc;

I know Oracle doesn't support joins for update statement so I tried the above query. Still it gives me ORACLE ORA-00969: missing ON keyword error

Answer 1

You might put "(" and ")". So write :

ON ( rowid = rid )

Answer 2

First of all, the syntax problem: when you merge using a subquery, you have to alias it to something. But more importantly, you don't need to use a subquery.

MERGE INTO Table_a USING Table_b ON (Table_a.PartNo = Table_b.PartNo) 
WHEN MATCHED THEN UPDATE SET Table_a.Material_Desc = Table_b.Material_Desc

First of all, you have to put the table you want to update into the INTO portion. Second, doing the join in a subselect and merging on rowids won't offer you any benefit that I can think of.