Re: Table update on joined tables
Date: 22 Mar 2003 09:37:58 -0800
Message-ID: <f28184a0.0303220937.2d9965d0_at_posting.google.com>
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';Received on Sat Mar 22 2003 - 18:37:58 CET