Re: SQL question: update on a join

From: Josef Huber <josef.huber_at_ennstal.at>
Date: 1997/04/15
Message-ID: <01bc4996$01a9d280$149a70c2@AnonymousIPX>#1/1

UPDATE IPLogWork

    SET PortID =

        (SELECT PortID
           FROM LogProtocolCacheWork
          WHERE PortNumber = IPLogWork.PortNumber
           AND ProtocolID = IPLogWork.Protocol )

WHERE (portnumber,protocol) = ANY
(SELECT portnumber,protocolld FROM LogProtocolCacheWork)

L. Tseng <lesliet_at_u.washington.edu> schrieb im Beitrag <5ilru4$b5e_at_nntp1.u.washington.edu>...
> Hi, Oracle experts,
>
> I have converted a Sybase/MS SQL code to Oracle which does an update on
 join like
> the following but it does not work the same. Can some SQL guru figure out
> a better way to do this?, thanks.
>
> Sybase/MS SQL code:
>
> UPDATE IPLogWork
> SET PortID = LogProtocolCacheWork.PortID
> FROM IPLogWork IPLogWork,
> LogProtocolCacheWork LogProtocolCacheWork
> WHERE IPLogWork.PortNumber = LogProtocolCacheWork.PortNumber
> AND IPLogWork.Protocol = LogProtocolCacheWork.ProtocolID
>
> Oracle SQL Code:
>
> UPDATE IPLogWork
> SET PortID =
> (SELECT PortID
> FROM LogProtocolCacheWork
> WHERE PortNumber = IPLogWork.PortNumber
> AND ProtocolID = IPLogWork.Protocol )
>
> All the IPLogWork.PortID become NULL if there is no match. This is
 incorrect.
>
> Leslie
>
  Received on Tue Apr 15 1997 - 00:00:00 CDT