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