Suggestions on a good way to write an Update with a join

From: <smb_slb_at_my-dejanews.com>
Date: Tue, 18 May 1999 21:34:09 GMT
Message-ID: <7hsmci$r1l$1@nnrp1.deja.com>

Hi,

Is there a way to write an update statement that affects several columns of a table, based on a join to another table, without using zillions of subqueries? This is easy in Sybase, but the syntax of Oracle seems to be stopping me.

For example, consider this table:

CREATE TABLE fu
 (PKFu1 integer
,PKFu2 integer
,PKRevision integer
,FuCol4 integer
,FuCol5 integer
,FuCol6 integer
,FuCol7 integer

 );

Let's say I want to copy all rows whose PKRevision column is 5 to the corresponding rows with PKRevision equal to 0. In Sybase, I could write the query this way:

  UPDATE Fu

     SET FuCol4 = f2.FuCol4
       , FuCol5 = f2.FuCol5
       , FuCol6 = f2.FuCol6

    FROM Fu f2

   WHERE Fu.PKFu1 = f2.PKFu1
     AND Fu.PKFu2 = f2.PKFu2
     AND f2.PKRevision = 5
     AND f2.PKRevision = 0

;

However, since Oracle doesn't like the FROM in the body of the update, I'm forced to write the update this way:

  UPDATE Fu

     SET FuCol4 =
         ( SELECT FuCol4
             FROM Fu f2
            WHERE Fu.PKFu1 = f2.PKFu1
              AND Fu.PKFu2 = f2.PKFu2
              AND f2.PKRevision = 5
         )
       , FuCol5 =
         ( SELECT FuCol5
             FROM Fu f2
            WHERE Fu.PKFu1 = f2.PKFu1
              AND Fu.PKFu2 = f2.PKFu2
              AND f2.PKRevision = 5
         )
       , FuCol6 =
         ( SELECT FuCol6
             FROM Fu f2
            WHERE Fu.PKFu1 = f2.PKFu1
              AND Fu.PKFu2 = f2.PKFu2
              AND f2.PKRevision = 5
         )
   WHERE PKRevision = 0
     AND EXISTS
         ( SELECT 1
             FROM Fu f2
            WHERE Fu.PKFu1 = f2.PKFu1
              AND Fu.PKFu2 = f2.PKFu2
              AND f2.PKRevision = 5
         )

;

I need the EXISTS in the WHERE clause to prevent any rows without a corresponding PKRevision = 5 from being set to 0.

I tried using this construction:

  UPDATE (

           SELECT fu.FuCol4
                , f2.FuCol4 new_FuCol4
                , fu.FuCol5
                , f2.FuCol5 new_FuCol5
                , fu.FuCol6
                , f2.FuCol6 new_FuCol6
             FROM Fu, Fu f2
            WHERE Fu.PKFu1 = f2.PKFu1
              AND Fu.PKFu2 = f2.PKFu2
              AND f2.PKRevision = 5
              AND f2.PKRevision = 0
         )
     SET FuCol4 = new_FuCol4
       , FuCol5 = new_FuCol5
       , FuCol6 = new_FuCol6

;

But I get this error:

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

It seems to me that I must have overlooked something, since in general Oracle has more features than Sybase. Is there a better way?

If you can, please respond to
sbroburg_at_concord.tt.slb.com, (but spell the first part as sbroberg) since my connection here slow.

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 18 1999 - 16:34:09 CDT