Oracle - update join - non key-preserved table
You should be able to do this with a correlated subquery
UPDATE tbl1 t1
SET t1.b = (SELECT c
FROM tbl2 t2
WHERE t1.id = t2.id
AND t1.a = t2.a
AND t1.b = t2.b
AND t2.d = 'a')
WHERE t1.a = 'foo'
AND EXISTS( SELECT 1
FROM tbl2 t2
WHERE t1.id = t2.id
AND t1.a = t2.a
AND t1.b = t2.b
AND t2.d = 'a')
The problem with the UPDATE that you've written is that Oracle cannot guarantee that there is exactly 1 tbl2.c value that corresponds to a single tbl1.b value. If there are multiple rows in tbl2 for any particular row in tbl1, the correlated update is going to throw an error indicating that a single-row subquery returned multiple rows. In that case, you'd need to add some logic to the subquery to specify which row from tbl2 to use in that case.
ORA-01779 cannot modify a column which maps to a non key-preserved table
I was able to run my query it by using EXIST clause
UPDATE FCT_RA F
SET F.V_CUST_NUMBER = ( SELECT CASE_TITL_NM
FROM KDD_CASES C
WHERE F.N_RA_ID = C.RA_ID
AND UPPER(CNTRY_KEY_ID) LIKE '%MANUAL%'
AND SCORE_CT IN (99,100)
AND STATUS_CD = 'CCD'
AND CASE_TITL_NM NOT LIKE 'MANUAL%')
WHERE EXISTS ( SELECT 1
FROM KDD_CASES C
WHERE F.N_RA_ID = C.RA_ID
AND UPPER(CNTRY_KEY_ID) LIKE '%MANUAL%'
AND SCORE_CT IN (99,100)
AND STATUS_CD = 'CCD'
AND CASE_TITL_NM NOT LIKE 'MANUAL%');
Trying to make a simple update but SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
You can use update but with a correlated subquery:
update table1 t1
set status = 'NEW'
where t1.status = 'COMPLETE' and
exists (select 1
from table2 t2
where t1.ID = t2.ID and t2.party is null
);
I suspect that you might also want a condition if there are no rows at all in table2 but that logic would not conform to your current query.
Update tables when cannot modify a column which maps to a non key-preserved table
Yes this is possible using two update statements:
update s
set op_id = (select j.journey_id
from j
where j.op_id = s.op_id
);
and:
update op
set op_id = (select j.journey_id
from j
where j.op_id = op.op_id
);
Updating a Join View without getting Non Key-Preserved Error
You cannot update column_from_table2 in this view, it's not updatable.
To check whether some column is updatable or not, run this query:
SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE table_name = 'MY_DATAVIEW';
OWNER TABLE_NAME COLUMN_NAME UPDATABLE INSERTABLE DELETABLE
------------------------------ ------------------------------ ------------------------------ --------- ---------- ---------
TEST MY_DATAVIEW KEY1 YES YES YES
TEST MY_DATAVIEW KEY2 YES YES YES
TEST MY_DATAVIEW COLUMN_FROM_TABLE1 YES YES YES
TEST MY_DATAVIEW COLUMN_FROM_TABLE2 NO NO NO
Why it is not updatable ? It's a big question.
There is a whole chapter in documentation on this topic:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/views001.htm#i1006234
find "Updating Views That Involve Outer Joins", there is an example of a view with outer join and detailed explanation which columns in this view can and cannot be updated and why.
Non-key-preserved table error for update with unique data
You know there is no duplication, but when the statement is parsed the optimiser doesn't know there isn't, and more importantly that there can't be, any duplication. The parser looks at statistics to decide how to work but in general doesn't look at data. The update statement may be cached and reused, so even if it did look and saw there were no duplicates right now, the data might have changed when the same statement is run again later from the cache.
Imagine you added an extra row:
insert into taby values(2,222);
then your query gets:
select x2,y2 from tabx,taby where tabx.x1=taby.y1;
X2 Y2
---------- ----------
11 21
12 22
12 222
Now the update has two possible values to set both x2=12 values to; should they both be 22, or both 222, or one of each? Oracle has no way of knowing what is right and can't choose which of the first two options should be used (and certainly can't use the third).
Now, that isn't your actual situation, but you need to tell Oracle that the situation cannot occur. The wording of the error is hint here; for the view to preserve a key, there must be a key. If you define tabY with a primary or unique key:
create table tabY (y1 number primary key, y2 number);
insert into taby values(1,21);
insert into taby values(2,22);
then my made-up third insert isn't allowed, Oracle knows that, and can apply that knowledge to the update:
update (select x2,y2 from tabx,taby where tabx.x1=taby.y1) set x2=y2;
2 rows updated.
select x2,y2 from tabx,taby where tabx.x1=taby.y1;
X2 Y2
---------- ----------
21 21
22 22
It doesn't matter how many rows you have with x1=2, you want them all to be updated to x2=22. So x1 doesn't need to be unique, and you don't need a UK/Pk on that. But y1 does have to be unique so you know which single value of y2 to use for all of those x1=2 rows.
What i don't understand is whether the view becomes key preserved based if the columns involved join are declared unique or the actual values that is getting updated.
It's the columns, not the values. But the bit I think I didn't explain well is that because you are doing set x2=y2 it can see that only the tabx table is actually being updated; so it needs to be able to identify which rows on the tabx table are being affected, and then it needs to work out for each of those rows which matching taby row to get the y2 values from - which it gets from the join condition.
For each x2 being updated it has to identify a single y2 value to use, which it does by looking for the taby row where taby.y1 is that row's tabx.x1. If there was - or just could be - multiple rows in taby which match that condition then it wouldn't know which of those options to use - 22 or 222 in my example. There has to be a single match in taby, so y1 has to be unique, and has to be declared as unique via a UK or PK.
There could be lots of different x2 values that get updated to the same y2 value; you could also have
insert into tabx values(2,13);
insert into tabx values(2,14);
etc.
and all of those would still be updated to the same value - 22 - because the non-unique x1 value in all of those rows still maps to a single ytab row with that tables UK/PK via the join condition.
How to get rid of ORA 01779 error :key preserved table
If there is no more than one row in table2 corresponding to table1 then use merge:
merge into table1 d
using table2 s
on (d.cg = s.cg and d.ce = s.ce)
when matched then update set
d.nbqe = s.nbqe, d.adr1 = s.adr1, d.adr3 = s.adr3
Only matching rows will be updated, rest remains untouched. You could do it with update, but this way You avoid repeating condition for where clause.
Test:
create table table1 (nbqe int, adr1 int, adr2 int, adr3 int, cg int, ce int);
create table table2 (nbqe int, adr1 int, adr2 int, adr3 int, cg int, ce int);
insert into table1 values (1, 1, 1, 1, 1, 1);
insert into table1 values (2, 2, 2, 2, 2, 2);
insert into table1 values (3, 3, 3, 3, 2, 2);
insert into table2 values (5, 5, 5, 5, 2, 2);
Result:
NBQE ADR1 ADR2 ADR3 CG CE
---- ---- ---- ---- ---- ----
1 1 1 1 1 1
5 5 2 5 2 2
5 5 3 5 2 2
Related Topics
Re-Writing "Fuzzy Join" Functions from R to SQL
How to Include Excluded Rows in Returning from Insert ... on Conflict
Combine Two Tables in Select (SQL Server 2008)
Limit Characters Returned in Oracle SQL Query
Mysql: Selecting Multiple Fields into Multiple Variables in a Stored Procedure
Applying the Min Aggregate Function to a Bit Field
Insert Multiple Rows into Db2 Database
Rodbc Queries Returning Zero Rows
Get All Dates in Date Range in SQL Server
What Is the Internal Representation of Datetime in SQL Server
Shredding Xml from Execution Plans