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

Your query doesn't really make sense. Why are you using `merge` and not a simple `update`? — Gordon Linoff, Jun 08 '16 at 14:00
So the error is "invalid identifier" or "invalid parameter"? In either cases, doesn't it have an associated ORA- code? — Álvaro González, Jun 08 '16 at 14:17
Unless your SPRENTHIERARCHIES table has both `rootcategoryId` and `root` columns, and your SPRENTCATEGORIES table has both `parentCategoryId` and `par` columns, then you checked that the wrong names existed. Maybe add the table definitions to the question to clarify. But this doesn't seem to really do anything, you would update to the same value when there is a match, if it worked. — Alex Poole, Jun 08 '16 at 14:28

Alex Poole · Accepted Answer · 2016-06-08T14:25:23.477

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.

I will be merging some more columns from SPRENTCATEGORIES in SPRENTHIERARCHIES and that's why I'm using merge. Yes, I'm using 'c' and 'e' because otherwise it gives me the "Columns referenced in the ON Clause cannot be updated" error. — Kavita Salvi, Jun 08 '16 at 20:12
@KavitaSalvi - you can't use merge if you're changing the column in the on clause, whatever name or alias you use. Changing it to make it even less valid won't help. If you're actually only updating other columns - since the update is currently pointless anyway - then you'll be OK. You can update multiple columns with an update statement too though. — Alex Poole, Jun 08 '16 at 20:16
Okay .. All I want to have is my SPRENTHIERARCHIES table updates, with some columns from SPRENTCATEGORIES , using c.PARENTCATEGORYID = h.rootCategoryID. How should I do this in oracle? — Kavita Salvi, Jun 08 '16 at 20:20
Just don't `SET SPRENTHIERARCHIES.rootcategoryId=...`. If you get other new values in the `using` clause you can update with those, you just can't change the `on` column. (It sounds like you're duplicating data, which is a bit odd). If that doesn't help then you should ask a new question explaining your requirement and showing data and expected result. — Alex Poole, Jun 08 '16 at 20:25
Yes .. Duplicating the columns wasn't the right way to do it. Anyway, I changed the code.. I could solve the problem by not duplicating the two columns. Thank you so much for your help! Cheers :) — Kavita Salvi, Jun 09 '16 at 13:34
@KavitaSalvi If Alex' answer solved your problem, please consider marking it as accepted (by clicking the checkmark button next to it). — Frank Schmitt, Jun 14 '16 at 07:14

Oracle: Invalid identifier

1 Answers1