Oracle: Invalid identifier

Question

I am using the following query in oracle. However, it gives an error saying that "c.par" in line 5 is an invalid parameter. No idea why. The columns exist. I checked. I have been struggling with this for a long time. All I want to do is to merge one table into another and update it using oracle. Could someone please help?

MERGE INTO SPRENTHIERARCHIES 
USING ( SELECT c.PARENTCATEGORYID AS par,  
             e.rootcategoryId AS root 
        FROM  SPRENTCATEGORIES c,SPRENTHIERARCHIES e 
        WHERE e.root (+)= c.par 
      ) SPRENTCATEGORIES  
ON (SPRENTHIERARCHIES.rootcategoryId = SPRENTCATEGORIES.parentcategoryId) 
WHEN MATCHED THEN 
UPDATE SET e.root=c.par 

Answer 1

The e and c aliases only exist within the query in the using clause. You're trying to refer to them in the update clause. You're also using a column alias from the using clause against the target table, which doesn't have that column (unless your tables have both rootcategoryId and root, and parentCategoryId and par).

So this:

UPDATE SET e.root=c.par 

should be:

UPDATE SET SPRENTHIERARCHIES.rootcategoryId= SPRENTCATEGORIES.par 

And in that using clause you're trying to use column aliases as the same level of query, so this:

    WHERE e.root (+)= c.par 

should be:

    WHERE e.rootcategoryId (+)= c.PARENTCATEGORYID

Your on clause is wrong too, as that is not using the column alias:

ON (SPRENTHIERARCHIES.rootcategoryId = SPRENTCATEGORIES.par) 

But I'd suggest you replace the old syntax in the using clause with proper join clauses:

MERGE INTO SPRENTHIERARCHIES 
USING ( SELECT c.PARENTCATEGORYID AS par,  
             e.rootcategoryId AS root 
        FROM  SPRENTCATEGORIES c
        LEFT JOIN SPRENTHIERARCHIES e 
        ON e.rootcategoryId = c.PARENTCATEGORYID 
      ) SPRENTCATEGORIES  
ON (SPRENTHIERARCHIES.rootcategoryId = SPRENTCATEGORIES.par) 
WHEN MATCHED THEN 
UPDATE SET SPRENTHIERARCHIES.rootcategoryId= SPRENTCATEGORIES.par  

You have a more fundamental problem though, as you're trying to update a joining column; this will get:

ORA-38104: Columns referenced in the ON Clause cannot be updated

As Gordon Linoff suggested you can use an update rather than a merge. Something like:

UPDATE SPRENTHIERARCHIES h
SET h.rootcategoryId = (
  SELECT c.PARENTCATEGORYID
  FROM SPRENTCATEGORIES c
  WHERE c.PARENTCATEGORYID = h.rootCategoryID
)
WHERE EXISTS (
  SELECT null
  FROM SPRENTCATEGORIES c
  WHERE c.PARENTCATEGORYID = h.rootCategoryID
)

The where exists clause is there in case there not be a matching record - which the outer join in your original query implies. But in this form it's even more obvious that you're going to update rootcategoryId to the same value, since you're selecting the parentCategoryID which is equal to it. So the update (or merge) seems to be pointless.