CODEWITHSUNDEEP
sqloracle-database
Cantillon
Cantillon

Reputation: 1638

Updating order of child record

I've got two tables:

Parent

| id |

Child

| id | owner | ordernr |

owner is a foreign key referencing Parent's id. There is a uniqueness constraint on (owner, ordernr)

Now, there are some gaps in the orders and I'm trying to fix them as follows:

CREATE OR REPLACE VIEW myView AS 
(SELECT childid, ordernr, n 
  FROM (SELECT child.id as childid, ordernr, ROW_NUMBER() OVER ( PARTITION BY parent.id ORDER BY ordernr) AS n 
        FROM Parent, Child WHERE owner = parent.id)
WHERE ordernr <> n)

UPDATE 
(SELECT c.ordernr, n
FROM Child c, myView WHERE childid = c.id) t 
SET t.ordernr = t.n

But I get: ORA-01779: cannot modify a column which maps to a non key-preserved table

Upvotes: 1

Views: 25

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

ORA-01779: cannot modify a column which maps to a non key-preserved table

This error occurs when you try to INSERT or UPDATE columns in a join view which map to a non-key-preserved table.

You could use a MERGE.

For example,

MERGE INTO child c
USING (SELECT n
       FROM   myview) t
ON (t.childid = c.id)
WHEN matched THEN
  UPDATE SET c.ordernr = t.n

/

Upvotes: 1

Related Questions