Re: SQL Help

"Douglas Cowles" <dcowles_at_us.ibm.com> wrote in message news:396B73D9.637B0FFF_at_us.ibm.com...
> I have two tables with identical layouts, 3 columns each. One contains
> rows
> I want to delete in the other.
>
> Say
> Table A (order number, orderitem number, copy char(12)); 20000 rows
> Table B( order number, orderitem number, copy char(12)); 4000 rows
>
> The three columns are part of a composite primary key..
> I would like to delete the rows in A that are present in table B..
> One way to do this
> is
> delete from A where
> order||orderitem||copy in (select order||orderitem||copy from B);
>
> That works fine.. but I was wondering if there was a way to do it
> without
> a string concatenation..
>
> If I try something like
> delete from A
> where exists (select 1 from A x, B y
> where x.order = y.order and x.orderitem = y.orderitem and x.copy =
> y.copy)
>
> it deletes all rows...
> What to do with subquery if you have to match 3 columns instead of 1?
>
> Thanks
> Dc.
>

The Correct queries are
delete from A where
 (order,orderitem,copy) in (select order,orderitem,copy from B);

Or

delete from A x
 where exists (select 1 B y
 where y.order = x.order and y.orderitem = x.orderitem and y.copy = x.copy)

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Jul 11 2000 - 00:00:00 CDT