CODEWITHSUNDEEP
sqloracle-database
Louwinho
Louwinho

Reputation: 21

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?

Upvotes: 1

Views: 194

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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
              );

Upvotes: 1

Related Questions