Re: question on delete statement

"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA703D0D575_at_lnewton.leeds.lfs.co.uk...
> Hi Fabio,
>
> DELETE FROM table_name
> WHERE some_condition;
>
> I don't expect your example statement to actually be accepted in SQLPlus
> or anything else for that matter. To me it has a sytax error as you did
> not specify the tablename to delete from. I haven't tested it - as I
> suspect is the case with you.
>
> Cheers,
> Norman.
>

Norman,

:) Welcome to 9i. Deletes based on join are acceptable. However, there are some rules.
I have not experimented much on join based deletes ... but the following example should show something:

Oracle 9.2.0.3 EE on Solaris

SQL> create table a1 (a number, b number, constraint a1_pk primary key (a)); Table created.

SQL> create table a2 (a number, b number, constraint a2_pk primary key (a)); Table created.

SQL> insert into a1 values (1,100);
1 row created.

SQL> insert into a1 values (2,100);
1 row created.

SQL> insert into a2 values (1, 100);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from a1;

         A B
---------- ----------

         1        100
         2        100

SQL> select * from a2;
         A          B
---------- ----------
         1        100

SQL> delete (select * from a1,a2 where a1.a = a2.a) c   2 where c.b = 100;
where c.b = 100

      *
ERROR at line 2:
ORA-00918: column ambiguously defined

SQL> delete (select a1.a a1_a, a1.b a1_b, a2.a a2_a, a2.b a2_b from a1,a2 where a1.a = a2.a) c
  2 where c.a1_b = 100
  3 /

1 row deleted.

SQL> select * from a1;

         A B
---------- ----------

         2 100

SQL> select * from a2;

         A B
---------- ----------

         1 100

SQL> rollback;

Rollback complete.

SQL> delete (select a1.a a1_a, a1.b a1_b, a2.a a2_a, a2.b a2_b from a1,a2 where a1.a = a2.a) c
  2 where c.a2_b = 100
  3 /

1 row deleted.

SQL> select * from a1;

         A B
---------- ----------

         2 100

SQL> select * from a2;

         A B
---------- ----------

         1 100

SQL> rollback;

Rollback complete.

SQL> delete (select a2.a a2_a, a2.b a2_b, a1.a a1_a, a1.b a1_b from a2,a1 where a1.a = a2.a) c
  2 where c.a2_b = 100
  3 /

1 row deleted.

SQL> select * from a1;

         A B
---------- ----------

         1        100
         2        100

SQL> select * from a2;

no rows selected

SQL> rollback;

Rollback complete.

SQL> delete (select a1.a a1_a, a1.b a1_b, a2.a a2_a, a2.b a2_b from a2,a1 where a1.a = a2.a) c
  2 where c.a2_b = 100
  3 /

1 row deleted.

SQL> select * from a1;

         A B
---------- ----------

         1        100
         2        100

SQL> rollback;

Rollback complete.

It seems to delete the rows of the table mentioned first in the from clause of the select statement.
The tables should be joined on their PK's!

Same goes with UPDATES ... you can now update based on the join clause.

NOTE: This is a limited test. Like I said ... I have not experimented much on join based updates/deletes.

HTH
Anurag Received on Fri Jun 20 2003 - 11:13:50 CDT