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

Looking at the SQL above, here is what I am thinking

1) myschema.tableA doesn't have ID column (it could be field_id)
2) The SELECT doesn't seem to provide a join condition

SELECT src.field_id FROM myschema.srcTable src INNER JOIN myschema.tableB tableB ON

Where is the condition to JOIN src with tableB?

Answer 2

Fields from the update can be used in the nested select as can be seen with the following test:

drop table test;
create table test as (select 1 key, 'a' value from dual);
insert into test values (2,'b');
select * from test;
update test t1 
   set value = (select 'c' from dual where t1.key=2);
select * from test;

I'm not sure why it doesn't work in this case. It seems like an explicit join with TableA may be necessary.

Answer 3

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

Answer 4

I must admit that I am not familiar with Oracle, but I am more or less beaten by SQL dialect of SQL Server. And the last time I looked, this SQL dialect does not allow to specify an alias for the table-to-be-updated in an UPDATE statement. I.e. in SQL Server, identifier update_tableA.id would be illegal. Is it possible that Oracle has the same restriction?