Delete invalid Details

3 Dec 2005 3:33 PM

Islamegy®

I need to delete invalid records in child table while parent have 2 Fields
PrimaryKey like this
==================================
Delete From AH_TasnFakaraat Where Master_ID,Fakra_ID not in (Select
Master_ID, Fakra_ID From SubMaster)
==============================

(,) is not acceptable in Where clause so if i wrote it like this:
===================================
Where Master_ID not in (Select Master_ID From SubMaster) AND Fakra_ID not in
(Select Fakra_ID From SubMaster)
===================================
Is it work as i want??

3 Dec 2005 3:42 PM

Steve Kass

Islamegy® wrote:

Show quote

>I need to delete invalid records in child table while parent have 2 Fields
>PrimaryKey like this
>==================================
>Delete From AH_TasnFakaraat Where Master_ID,Fakra_ID not in (Select
>Master_ID, Fakra_ID From SubMaster)
>==============================
>
>
>(,) is not acceptable in Where clause so if i wrote it like this:
>===================================
>Where Master_ID not in (Select Master_ID From SubMaster) AND Fakra_ID not in
>(Select Fakra_ID From SubMaster)
>===================================
>Is it work as i want??
>
>
>
>

No, because this second query will fail to delete rows where
Master_ID and Fakra_ID appear in the table SubMaster, but not
in the same row of that table. In order to delete all rows
where the two IDs do not appear together, in the same row
of SubMaster, try this:

delete from AH_TasnFakaraat
where not exists (
select * from SubMaster
where SubMaster.Master_ID = AH_TasnFakaraat.Master_ID
and SubMaster.Fakra_ID = AH_TasnFakaraat.Fakra_ID
)

Steve Kass
Drew University

4 Dec 2005 9:04 AM

Islamegy®

Thanx alot. I wasn't know about not exist before..
thanx again

Show quote

"Steve Kass" <sk***@drew.edu> wrote in message
news:%23uAP8AC%23FHA.2676@TK2MSFTNGP15.phx.gbl...
>
>
> Islamegy® wrote:
>
>>I need to delete invalid records in child table while parent have 2 Fields
>>PrimaryKey like this
>>==================================
>>Delete From AH_TasnFakaraat Where Master_ID,Fakra_ID not in (Select
>>Master_ID, Fakra_ID From SubMaster)
>>==============================
>>
>>
>>(,) is not acceptable in Where clause so if i wrote it like this:
>>===================================
>>Where Master_ID not in (Select Master_ID From SubMaster) AND Fakra_ID not
>>in (Select Fakra_ID From SubMaster)
>>===================================
>>Is it work as i want??
>>
>>
> No, because this second query will fail to delete rows where
> Master_ID and Fakra_ID appear in the table SubMaster, but not
> in the same row of that table. In order to delete all rows
> where the two IDs do not appear together, in the same row
> of SubMaster, try this:
>
> delete from AH_TasnFakaraat
> where not exists (
> select * from SubMaster
> where SubMaster.Master_ID = AH_TasnFakaraat.Master_ID
> and SubMaster.Fakra_ID = AH_TasnFakaraat.Fakra_ID
> )
>
> Steve Kass
> Drew University

3 Dec 2005 3:43 PM

Tom Moreau

Use NOT EXISTS:

Delete From AH_TasnFakaraat
Where not exists
(
    Select
        *
    From SubMaster s
    where
            s.Master_ID = AH_TasnFakaraat.Master_ID
    and   s.Fakra_ID = AH_TasnFakaraat.Fakra_ID
)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada t**@cips.ca
www.pinpub.com

Show quote

"Islamegy®" <NULL_Islamegy_N***@yahoo.com> wrote in message
news:utdZu7B%23FHA.1536@TK2MSFTNGP15.phx.gbl...
>I need to delete invalid records in child table while parent have 2 Fields
>PrimaryKey like this
> ==================================
> Delete From AH_TasnFakaraat Where Master_ID,Fakra_ID not in (Select
> Master_ID, Fakra_ID From SubMaster)
> ==============================
>
>
> (,) is not acceptable in Where clause so if i wrote it like this:
> ===================================
> Where Master_ID not in (Select Master_ID From SubMaster) AND Fakra_ID not
> in (Select Fakra_ID From SubMaster)
> ===================================
> Is it work as i want??
>

4 Dec 2005 2:40 AM

--CELKO--

Besides the solutions given, have you thought about using DRI actions,
ON DELETE CASCADE and ON UPDATE CASCADE clauses.