Re: update table using a subquery on another table

Thanks to all.

Its starting to make a little sense now.

I will try adding the additional where clause.

TurkBear wrote:

>I may be misreading the logic here ( it is Friday after all) , but it appears that your code will update ALL the records your
>billings_detail_copy table regardless of whether there is matching data in the billdetail_2002 one...with NULL values being
>set when the where clause of the sub-query fails....
>
>Add a where clause ( the same one as in the sub-query) OUTSIDE the sub-query to limit the update to only those records that
>have a match in billdetail_2002....
>Like this:
>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
> )
> where a.applid = b.applid
> and a.childid = b.childid
> and a.prog_year = b.prog_year
> and a.prog_month = b.prog_month
>;
>
>
>Richard Booker <rbooker_at_21stcentury.net> wrote:
>
>>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.
>>
>>
>>
>>
>
>
>
>-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
> http://www.newsfeed.com The #1 Newsgroup Service in the World!
>-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----
>
Received on Sat Jun 22 2002 - 04:51:28 CDT