I jolly well have preserved my keys but Oracle doesnt believe me...

[cjard]

Hello


I'm trying to run and update in the following way:


UPDATE
(
SELECT * FROM
some_table
INNER JOIN
(
SELECT primary_key FROM
(
SELECT
primary_key,
row_number() OVER(PARTITION BY some_columns_with_duplicates ORDER BY primary_key) AS window_function_result
FROM
some_table
) window_dupe_counter
WHERE window_function_result > 1
) pks_for_dupes
USING(primary_key)
)
SET
col1 = 'whatever'


What we have, essentially is a list of calls and some call events occur twice per call when then shouldnt. So we partition by them and we can thus get a rownumber of greater than 1 for all the duplicates:

key,call_id,call_event,window_result
k1,call1,evt1,1 <-- this is event 1 occuring in the first call
k2,call1,evt1,2 <-- this is a duplicate event 1 occurring in the first call, rownumber is 2
k3,call2,evt1,1 <-- this is the first event from call 2


so the result of WHERE window_function_result > 1 should be from the above data set just a SINGLE primary key of: k2

I have checked that the list of PKs returned by WHERE window_function_result > 1 is such that they are all unique, and they are.

this, when inner joined back to the same table MUST produce a list of key-preserved rows.


Essentially, my query is:

UPDATE
SELECT * FROM some_table
INNER JOIN
(subset of unique PKs from that same some_table)
SET
blah...



Oracle doesnt believe me that I have preserved the keys. How can I assure it?




Here is some test data/script to try and play yourself:


--make and insert values
create table some_table (pk number primary key, call number, evt number);
insert into some_table values(1,1,1);
insert into some_table values(2,1,1);
insert into some_table values(3,2,1);

--lets see that list of pks
SELECT pk FROM
(
SELECT
pk,
row_number() OVER(PARTITION BY call, evt ORDER BY pk) AS window_function_result
FROM
some_table
) window_dupe_counter
WHERE window_function_result > 1;

--lets see that list of pks joined back to the original table
SELECT * FROM
some_table
INNER JOIN
(
SELECT pk FROM
(
SELECT
pk,
row_number() OVER(PARTITION BY call, evt ORDER BY pk) AS window_function_result
FROM
some_table
) window_dupe_counter
WHERE window_function_result > 1
) pks_for_dupes
USING(pk);


--try the update
UPDATE
(
SELECT * FROM
some_table
INNER JOIN
(
SELECT pk FROM
(
SELECT
pk,
row_number() OVER(PARTITION BY call, evt ORDER BY pk) AS window_function_result
FROM
some_table
) window_dupe_counter
WHERE window_function_result > 1
) pks_for_dupes
USING(pk)
)
SET
evt = -1


Oracle error "Cannot modify a column which maps to a non key-preserved table"