i use ORACLE SQL and i want to Update a table with conditions. One Condition is that in a other database the table is the value null. But i get the error 'Table, view or sequence reference 'Db2table.MERGE_UNIT_ID' not valid in this context.' For this I create a new table with the column of the table of DB2 and i want to use it to compare it.
create table db2table as (select r.merge_einheit_id from be_einheit be join DB2.r_be_einheit r on be.id = r.id)
begin
if db2table.merge_einheit_id is null
then Update be_einheit set be_einheit.CHDATE=sysdate
where be_einheit.merge_einheit_id is not null
and be_einheit.id= be_container.einheit_id;
end if;
end;
my second approch is that i add it to the WHERE with an AND, but then i get the Error '"DB2"."MERGE_EINHEIT_ID":invalid identifier'. Anyone have any idea what this is or how i can get adas problem solved?
begin
Update be_einheit set be_einheit.CHDATE=sysdate
where be_einheit.merge_einheit_id is not null
and be_einheit.id= be_container.einheit_id
and db2table.merge_einheit_id is null;
end;```
Creating a table won't help much in long term because that table is a static object; if anything changes in tables you used in CTAS, db2table won't know anything about it.
If you successfully created db2table, then - according to join condition (join **DB2.**r_be_einheit on) - you aren't actually talking about other database - it is just another schema in the same database. Furthermore, it means that owner (db2) granted (at least) select privilege on r_be_einheit to you. That's OK.
To me, it looks as if something like this might do:
update be_einheit be set
be.einheit.chdate = sysdate
where exists (select null
from db2.r_be_einheit r
where r.id = be.id
and r.merge_einheit_id is null
)
and exists (select null
from be_container bc
where bc.einheit_id = be.id
)
and be.merge_einheit_id is not null;