Clever Geek Handbook

In SQL, can I use a variable bounded by the nested select within the nested select itself?

I am 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 use a variable bounded by the nested selection within the nested selection? Any thoughts?

PS ID is really valid in the database table. The problem is in scope, but I want to make sure that this is indeed the problem.

+1


source to share


4 answers


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

Try the following:



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


source


After looking at the SQL above, here is what I think

1) myschema.tableA has no column id (it could be field_id)
2) SELECT doesn't seem to enforce a join condition



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

Where is the JOIN src with tableB?

0


source


The fields from the update can be used in the nested element, as seen in 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 that an explicit connection to TableA might be necessary.

0


source


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

0


source






More articles:


All Articles