I'm trying to run and update in the following way:
CODE
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'
(
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:
CODE
UPDATE
SELECT * FROM some_table
INNER JOIN
(subset of unique PKs from that same some_table)
SET
blah...
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:
CODE
--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
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