Re: update table using a subquery on another table

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 Fri Jun 21 2002 - 10:06:12 CDT