Re: SQL question: update on a join

L. Tseng wrote:
>
> 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.

  UPDATE IPLogWork
    SET PortID =

        (SELECT PortID
           FROM LogProtocolCacheWork
          WHERE PortNumber = IPLogWork.PortNumber
            AND ProtocolID = IPLogWork.Protocol )
    WHERE PortID in (select portid from LogProtocolCacheWork
                     WHERE PortNumber = IPLogWork.PortNumber
                     AND ProtocolID = IPLogWork.Protocol )

---
Name   : Lun Wing San (Certified Oracle Database Administrator)

Title  : Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841

This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.