Re: Delete with join

Larry Coon <lmcoon_at_home.com> wrote in message news:38E7C539.30BB_at_home.com...
> Can somebody tell me how to do in Oracle 8
> what I can do in Sybase? Say I have the
> following tables:
>
> X
> val1 val2 (val1 is PK)
> 10 20
> 30 40
>
> Y
> val3 val4 (val1 is PK, val4 is FK to X.val1)
> 1 10
> 2 10
> 3 30
> 4 30
>
> I want to delete specific rows from Y, after
> performing a natural join on X and Y and looking
> for specific conditions in X. For example, delete
> all rows in Y where the corresponding X.val2 is
> the largest X.val2 in X. Here is the Sybase syntax:
>
> delete y
> from x, y
> where x.val1 = y.val4
> and x.val2 = (select max(val2) from x)
>
> I couldn't find corresponding Oracle syntax in my
> Oracle documentation. Is there a way to do this
> in Oracle?
>
> Thanks,
>
> Larry Coon
> lmcoon_at_home.com

Yes, Transform the join in a subquery.

delete
from y
where y.val4 =
(select val1
 from x
 where x.val2 = (select max(val2) from x) )

Hth,

Sybrand Bakker, Oracle DBA Received on Sun Apr 02 2000 - 17:23:07 CDT