I can't seem to get my head wrapped around this.
I have two tables:



One has Daily Tansactions (compl_trxd) and is keyed on trxd_plan_num (
as well as other fields)
One has new plans (compl_nplan) and is keyed on nplan_plan_num.

Every day I load new plans that were created today into the compl_nplan
table
I then drop the Daily Transaction table, and reload with todays
transactions (contains all plans that transacted including old ones).
I compare the two tables and report on any plans that had its first
transaction.

I then want to remove these plans from the compl_nplan table that had a
transaction that day ( leaving plans with no transactions left intact
since I only show the plans intitial transactions, and then remove them
from further reporting)

I tried the following sql - but it deletes all the records from
compl_nplan - when it should on deelte a tiny fraction.

DELETE FROM Compl_nplan
WHERE exists ( select trxd_plan_num from
compl_trxd_complete,compl_nplan
where trxd_plan_num = nplan_plan_num
group by trxd_plan_num
)

On 4 May 2006 08:42:26 -0700, "Alt255" <alt255.2005 (AT) gmail (DOT) com> wrote:

delete from comp_nplan c
where exists
(select 'x'
from comp_trxd_complete t
where t.trxd_plan_num = c.nplan_plan_num
)


--
Sybrand Bakker, Senior Oracle DBA