Re: Table update on joined tables

gardnej_at_bestwestern.com (J Gardner) wrote in message news:<b0f265c8.0303211312.482568c_at_posting.google.com>...
> I come from a SQL Server background, but we are converting to Oracle
> [8.1.7] on HP-UX.
>
> How would I translate the following update:
>
> UPDATE M
> SET M.PROPERTY_ID = R.RESORT,
> M.REDEEM_DATE = R.REDEEM_DATE,
> M.REDEEM_USER = R.REDEEM_USER,
> M.VOUCHER_STATUS = 'REDEEMED',
> M.UPDATE_DATE = R.UPDATE_DATE,
> M.UPDATE_USER = R.UPDATE_AGENT
> FROM AWARD A,
> TYPE T,
> REDEMPTION R,
> MW_REDEMPTION M
> WHERE A.TYPE_ID = T.TYPE_ID
> AND A.AWARD_ID = R.AWARD_ID
> AND A.SERIAL_1 = M.VOUCHER_ID
> AND A.SERIAL_2 = M.VOUCHER_SERIAL
> AND M.VOUCHER_STATUS = 'ISSUED';
>
> to a query that Oracle 8.1.7 can recognize???

Without being able to test it this would be my first attempt:

            UPDATE  MW_REDEMPTION M
            SET     (M.PROPERTY_ID,M.REDEEM_DATE,M.REDEEM_USER,
                    M.VOUCHER_STATUS,M.UPDATE_DATE,M.UPDATE_USER)
            =
                    (SELECT R2.RESORT,R2.REDEEM_DATE,R2.REDEEM_USER,
                    'REDEEMED',R2.UPDATE_DATE,R2.UPDATE_AGENT
                     FROM    AWARD A2,
                             TYPE T2,
                             REDEMPTION R2
                     WHERE   A2.TYPE_ID = T2.TYPE_ID
                     AND     A2.AWARD_ID = R2.AWARD_ID
                     AND     A2.SERIAL_1 = M.VOUCHER_ID
                     AND     A2.SERIAL_2 = M.VOUCHER_SERIAL
                     AND     M.VOUCHER_STATUS = 'ISSUED'
                    )
            WHERE   (M.VOUCHER_ID,M.VOUCHER_SERIAL)
            IN      (SELECT A.SERIAL_1,A.SERIAL_2
                     FROM    AWARD A,
                             TYPE T,
                             REDEMPTION R
                     WHERE   A.TYPE_ID = T.TYPE_ID
                     AND     A.AWARD_ID = R.AWARD_ID
                     AND     A.SERIAL_1 = M.VOUCHER_ID
                     AND     A.SERIAL_2 = M.VOUCHER_SERIAL
                     AND     M.VOUCHER_STATUS = 'ISSUED'
                     )
            AND      M.VOUCHER_STATUS = 'ISSUED';