Re: query help

From: <tamkatten_at_gmail.com>
Date: Thu, 21 Feb 2008 06:48:28 -0800 (PST)
Message-ID: <a83bca90-b2df-4e04-9a28-cb94f3bf6eca@z70g2000hsb.googlegroups.com>

On 21 Feb., 15:02, "www.douglassdavis.com" <douglass_da..._at_earthlink.net> wrote:
> Say I have two tables:
>
> table_a
> ----------
> a_id (primary key)
> b_id
>
> table_b
> ----------
> b_id (primary key)
> name
>
> there is a one to many mapping between rows in table b and rows in
> table a.
>
> Say I had an Id of a row in table a an (a_id, say 5).  Now, what I
> want to do, is delete the row in table_a (easy enough), but I also
> want to delete related rows in table_b, if they are ONLY related to
> the a_id of 5.
>
> In other words, I want to delete the row from table b (related to
> a_id), but I don't
> want to delete the row from table b that may be in use by another row
> in
> table a.
>
> How could I construct a query (or 2) to do that?

Hi,

Here's 4 ways to do the deletion from b, they combine the use of IN and EXISTS. The achieve the same logically, but they may perform very differently depending a.o. on the size of table A and B.

delete from b
where b_id in (select b_id

                 from a
                where a_id = 5 )

and not exists (select 1 from a where b_id = b.b_id and a_id != 5);

delete from b
where b_id in (select b_id

                 from a
                where a_id = 5 )

and b_id not in (select b_id from a where a_id != 5);

delete from b
where exists (select 1 from a where b_id = b.b_id and a_id = 5) and not exists (select 1 from a where b_id = b.b_id and a_id != 5);

delete from b
where exists (select 1 from a where b_id = b.b_id and a_id = 5) and b_id not in (select b_id from a where a_id != 5);

• Kenneth Koenraadt

Received on Thu Feb 21 2008 - 08:48:28 CST