Re: How do I do this update better ?

Steve wrote:
> I want to join a table to itself to get rows that are related to each
> other and then
> update row rev with values in row orig. It tried this update but is is
> doing a full
> table scan. Are there better ways to do this?
>
> Thanks
>
> S
>
> update modn.mn_sale rev
> set (rev.INV_QTY, rev.validation_status)
> = (select orig.inv_qty,
> orig.validation_status
> from modn.mn_sale orig
> where orig.reversal_status = 50
> and rev.reversal_status = 30
> and rev.BACKWARD_REF_ID = orig.SALE_ID
> and ABS(rev.INV_QTY) <> ABS(orig.INV_QTY))
>

What verison of Oracle?
What indexes exist?
Are statistics current with DBMS_STATS?

What is wrong with a full table scan? Without more information about what you are doing and your data this may be the most efficient solution.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org