Re: Delete with join

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Sun, 02 Apr 2000 23:46:00 GMT
Message-ID: <8c8m3l$aig$1@nnrp1.deja.com>

In article <38E7C539.30BB_at_home.com>,
lmcoon_at_home.com wrote:
> 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
>

Delete from the JOIN. The syntax is no different from a delete (delete works on Tables, views and subqueries). It might look like this:

ops$tkyte_at_8i> create table x ( val1 int primary key, val2 int ); Table created.

ops$tkyte_at_8i> create table y ( val3 int, val4 int references x ); Table created.

ops$tkyte_at_8i> insert into x values ( 10, 20 ); 1 row created.
ops$tkyte_at_8i> insert into x values ( 30, 40 ); 1 row created.

ops$tkyte_at_8i> insert into y values ( 1, 10 ); 1 row created.
ops$tkyte_at_8i> insert into y values ( 2, 10 ); 1 row created.
ops$tkyte_at_8i> insert into y values ( 3, 30 ); 1 row created.
ops$tkyte_at_8i> insert into y values ( 4, 30 ); 1 row created.

ops$tkyte_at_8i> delete from ( select y.*

  2                           from x, y
  3                          where x.val1 = y.val4
  4                            and x.val2 = ( select max(val2) from x )
  5                       )

  6 /

2 rows deleted.

--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Apr 02 2000 - 18:46:00 CDT