Help - Search - Members - Calendar
Full Version: Cannot modify a column which maps to a non key-preserved table
Oracle DBA Forums > Oracle > Oracle Forum
matt
Hello


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'



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...



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
Anand Desai
Hello,

Did you get a solution to this? I am executing a statement as:

UPDATE (SELECT a.pkcol key1,
b.pkcol key2,
a.message message1,
b.message message2,
a.active_status active_status1,
b.active_status active_status2
FROM table_name @dblarchive a, table_name b
WHERE a.pkcol = b.pkcol
AND a.key = 'SomePKValue') x SET message1 = message2, active_status1 = active_status2
WHERE key1 = key2
AND key1 = 'SomePKValue';

which involes dblik as well. I get the same error and many other tables it works fine. I have seen the table in question and that itself has all PK defined properly.

Other tables in the database works fine.


Thanks.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.