Re: Table update on joined tables

That worked perfectly...

Thanks.

michael_draves_at_hotmail.com (Michael Draves) wrote in message news:<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 Mon Mar 24 2003 - 18:54:17 CET