In SQL, can I use a variable scoped outside a nested select within the nested select itself?

Question

I'm trying to get the following SQL statement to work:

UPDATE myschema.tableA update_tableA
   SET field_id = 
   ( SELECT src.field_id 
         FROM myschema.srcTable src
         INNER JOIN myschema.tableB tableB ON 
            update_tableA.id = tableB.id 
            AND SDO_ANYINTERACT( tableB.shape, src.shape ) = 'TRUE' );

When I run this statement, I get the following error:

ORA-00904: "UPDATE_TABLEA"."ID": invalid identifier

Can I not use a variable scoped outside of the nested select within the nested select? Any thoughts?

P.S. The identifier is indeed valid in the database table. The problem appears to be scope, but I want to make sure that is indeed an issue.

Answer 1

I don't believe you can JOIN on a column (i.e. use it in the ON clause) that's not in one of the tables being joined. Additional predicates need to be in a WHERE clause.

Try this:

UPDATE myschema.tableA update_tableA
   SET field_id = 
   ( SELECT src.field_id 
         FROM myschema.srcTable src
         INNER JOIN myschema.tableB tableB ON 
           SDO_ANYINTERACT( tableB.shape, src.shape ) = 'TRUE'
        WHERE update_tableA.id = tableB.id 
   );