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

From: dhkn <daghk_at_online.no>
Date: Fri, 21 May 1999 12:10:47 +0200
Message-ID: <37453126.6D5291C7@online.no>

you should be able to to this:

 UPDATE Fu

     SET (FuCol4,FuCol5,FuCol6)  =
         ( SELECT FuCol4,FuCol5,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
         )

You now have 1 subquery instead of 3.
You are right in that you need the exists. There is no way of getting round that, unleass you are absolutely sure that the subquery does return a row.

smb_slb_at_my-dejanews.com wrote:

> 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 Fri May 21 1999 - 05:10:47 CDT