How is this NOT key preserved?
Suppose we have a table A
Its PK has 3 columns, X, Y and Z
The table is to be joined to itself:
SELECT * FROM
a src INNER JOIN a dst ON src.x=dst.x AND src.y = dst.y
src.z = 'source' AND
dst.z = 'dest'
For any given row that has a Z column of text "source"
There can only be one other row having matching X and Y columns and a Z column of dest
The whole of the PK is accounted for, and there either exists a pair of rows, or there not exists a pair of rows
But this view is not updatable according to oracle.. How can it be written so the optimizer believes that?
Thanks in advance
Use a merge instead of an update?
I did in the end.. it just irritates me that it doesnt work when it should!
Click Here to Expand Forum to Full Width