DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How is this NOT key preserved?

  1. #1
    Join Date
    Mar 2006
    Posts
    74

    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:

    Code:
    SELECT * FROM
      a src INNER JOIN a dst ON src.x=dst.x AND src.y = dst.y
    WHERE
      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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Use a merge instead of an update?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Mar 2006
    Posts
    74
    I did in the end.. it just irritates me that it doesnt work when it should!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width