Re: Delete query from Matching Records

On 4 May 2006 08:42:26 -0700, "Alt255" <alt255.2005_at_gmail.com> wrote:

>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
>)

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