Re: update table using a subquery on another table

There is an item on the FAQ:

http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Under PERFORMANCE topics, with the title:

    What is the most efficient way to update     one table with values from another table ?

This may give you some ideas.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Richard Booker wrote in message <3D12875A.7090503_at_21stcentury.net>...


>I am using Oracle 8i SQL*PLUS and have an update problem.

>

>I need to update a table named 'billings_detail_copy' (a copy of my real

table) with corresponding values stored in a table named 'billdetail_2002'.


>

>There are about 40,000 records in 'billings_detail_copy'.

>

>There are about 150 records in 'billdetail_2002'.

>

>The primary key for both tables is:

>applid NUMBER

>+ childid NUMBER

>+ actv_no VARCHAR2(6)

>+ prog_year NUMBER

>+ prog_month NUMBER.

>

>The following update statement results in 55,000+ records updated.

>

>=================

>update billings_detail_copy a

>set ( a.ft_elig_days, a.ft_attend,

> a.pt_elig_days, a.pt_attend,

> a.ex_hrs_attend,

> a.assess_fees, a.collect_fees ) =

> ( select b.ft_elig_days, b.ft_attend,

> b.pt_elig_days, b.pt_attend,

> b.ex_hrs_attend,

> b.assess_fees, b.collect_fees

> from billdetail_2002 b

> where a.applid = b.applid

> and  a.childid = b.childid

> and  a.prog_year = b.prog_year

> and  a.prog_month = b.prog_month

> )

>;

>==================

>

>Any help would be appreciated.

>

>

>

>

>