Using inner join when updating in Oracle

I have 2 tables: I want to update my table1 records with the suitable age, which can be found in table2. Unique identifier is the BVD_ID_NUMBER. I tried to do this using the following code

UPDATE table1
  SET table1.age = 
  (select table2.age2
    from 
      (select distinct table2.BVD_ID_NUMBER, table2.age2
        FROM table1
       inner JOIN table2
        on table1.ACQUIROR_BVD_ID_NUMBER=table2.BVD_ID_NUMBER)
   where table2.BVD_ID_NUMBER=table1.ACQUIROR_BVD_ID_NUMBER);

I received the following error: SQL Error: ORA-00904: "ORBIS_DISTINCT"."BVD_ID_NUMBER": invalid identifier 00904. 00000 - "%s: invalid identifier"

Any help?

Answers


Hmmm. You have overcomplicated your query. Usually, when using correlated subqueries, there is no reason to mention the outer table in the inner subquery. Oracle doesn't allow scoping beyond one level for correlated subqueries, so you need to simplify for the correlation clause:

UPDATE table1 t1
    SET age = (select t2.age2
               from table2 t2
               where t1.ACQUIROR_BVD_ID_NUMBER = t2.BVD_ID_NUMBER
              );

This is likely to cause a "subquery returns more than one row" type of error. To fix that, use aggregation or rownum = 1:

UPDATE table1 t1
    SET age = (select t2.age2
               from table2 t2
               where t1.ACQUIROR_BVD_ID_NUMBER = t2.BVD_ID_NUMBER and
                     rownum = 1
              );

Need Your Help

Do I want to use NSString filePaths or NSURLs?

iphone ios

Lots of classes have versions that work with NSStrings as a filePath or NSURLs. One example:

Missing little red "x" in Eclipse explorer and problems view for Java source with compile errors?

java eclipse compilation warnings

The little red 'x' on my Java class files in the project explorer (or package explorer) are missing. They were there...now they are gone.