Hi,
I am using Oracle 12c and here is my Question:
/*
ledgerstb is a Oracle IOT (TransNo Column is Primary Key)
vledgerstb_gtt is a Global Temporary Table
vledgervc_gtt is a Global Temporary Table
*/
UPDATE
(SELECT ledgerstb.TransNoVC AS TransNoVC_Old,
vledgervc_gtt.transnovc AS TransNoVC_New
FROM ledgerstb
INNER JOIN vledgerstb_gtt ON ledgerstb.TransNo = vledgerstb_gtt.transnostb
INNER JOIN vledgervc_gtt ON ledgerstb.STBNo = vledgervc_gtt.STBNo
) T
SET T.TransNoVC_OLD = T.TransNoVC_NEW;
This update Statement Gives Following Error:
Error report -
ORA-01779: cannot modify a column which maps to a non key-preserved table
ORA-06512: at "RELYC.STBPKG", line 597
ORA-06512: at line 201
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.
For your Ref. the Inner Select Statement (In Blue color Font) Give Following Result;
TransNo : 1 STBNo : VAAAABM315711131
TransNo : 2 STBNo : VAAAABM315711214
TransNo : 3 STBNo : VAAAABM315711262
TransNo : 4 STBNo : VAAAABM316410986
Pls. Suggest if I am doing Something Wrong or its not possible to update table Like This.
(I Know I can use ForAll Statement to Update but i don't want to use Collections Here.)