update table using a subquery on another table

From: Richard Booker <rbooker_at_21stcentury.net>
Date: Thu, 20 Jun 2002 20:54:34 -0500
Message-ID: <3D12875A.7090503@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. Received on Thu Jun 20 2002 - 20:54:34 CDT