FYI - New syntax allows you to update one table while joining to others

From: <sbrendan_at_my-deja.com>
Date: Thu, 19 Oct 2000 14:57:13 GMT
Message-ID: <8sn244$2rj$1@nnrp1.deja.com>

This syntax allows you to update one table while joining to many others - I find this simplier that doing sub-queries. This is similar to how other db servers work. Hope you find this interesting!

Here is an example:
update
(

    select

            a.TOTAL_POSITION_VALUE_AMT,
            a.TOTAL_SHARE_QTY,
            b.FIN_INSTRUMENT_PRC
    from    A_FPBAL_AUG_SEP_OCT_NOV    a,
            MSC_FIN_INSTRUMENT_PRICES   b
    where   a.MONTH_TIME_ID     = b.MONTH_TIME_ID
    and     a.FIN_INSTRUMENT_ID = b.FIN_INSTRUMENT_ID
    and     b.MONTH_TIME_ID     = 36799

)
set a.TOTAL_POSITION_VALUE_AMT = a.TOTAL_SHARE_QTY * b.FIN_INSTRUMENT_PRC
;

-- ===============================================

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 19 2000 - 09:57:13 CDT