0

I am trying to run an update statement that changes a field based on flags in two other tables in two different schemas. "IND" and "GRO" are in different schemas than the main_table mt, and different schemas from each other.

This is the code I am trying - but I am getting the invalid identifier (since I have not referred to the schema.table elsewhere I know this code is wrong).

I have tried using schema.table.field, but I get the invalid identifier error as well. I do not have permissions to make a synonym for the schema.table.

What is the correct way to do this or is there a better way to write this?

main_table -> mt.indID

mt.IndID = schema1.ind.indID
mt.IndID = schema2.gro.indID
schema1.ind.indID = schema2.gro.indID

IndId: 12345 across all tables.

mt_original_field = 1 or 0 (depending on previous update)
ind.inv1 = 0,1,2
gro.indID = 01,02,03,04,05,06,07,08


update main_table mt
set mt.original_field = case 
when ind.inv1 <> '2' and gro.dc1 not in ('01')
then 1
else mt.original_field
end;

Expected Outcomes:

ind.inv1 gro.dc1 Outcome
1 01 original_field
2 3 1
2 01 original_field

The next query depends on the outcome of this query as to whether or not it needs to run another update from another table, in another schema.

Adjanti
  • 1
  • 1
  • 1
    Please provide sample data and desired results. A non-working query is not necessarily very informative. – Gordon Linoff Dec 21 '20 at 16:29
  • @Adjanti what's the relation between tables ? please provide sample data of each table – eshirvana Dec 21 '20 at 16:44
  • It sounds like you want to write a correlated update https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table/7031405#7031405 You'd need to explain to us how the three tables relate to each other in order for us to help you write that correlated update (table definitions, sample data, expected results). – Justin Cave Dec 21 '20 at 16:44
  • Added some more information - hopefully that will help. – Adjanti Dec 21 '20 at 16:56
  • 1
    In addition to the other comments about providing more information, consider that you are going to have to issue some sort of SELECT against those two reference tables, else how is oracle supposed to know which row of those tables it is supposed to reference? – EdStevens Dec 21 '20 at 21:38

0 Answers0