SQL question: update on a join

From: L. Tseng <lesliet_at_u.washington.edu>
Date: 1997/04/11
Message-ID: <5ilru4$b5e@nntp1.u.washington.edu>#1/1

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 Fri Apr 11 1997 - 00:00:00 CDT