Re: Newbie: delete from multiple tables?

"Ian McCall" <ian_at_eruvia.org> wrote in message news:7ZxRa.11201$ju6.208202_at_newsfep4-glfd.server.ntli.net...
> Hello.
>
> I'm moving over from Sybase, and am looking for the equivalent syntax to
the
> following command:
>
>
> delete table_1
> from table_1, table_2
> where table_1.field1 = table_2.field1
> and table_1.field2 = table_2.field2
>
> In other words delete, only from table_1, anything where field1 and field2
> match a row in table_2.
>
> Any ideas?

SQL> create table t1 (id number primary key,col2 number);

Table created.

SQL> create table t2 (id number primary key,col2 number);

Table created.

SQL> insert into t1 values (1,1);

1 row created.

SQL> insert into t1 values (2,1);

1 row created.

SQL> insert into t1 values (2,2);
insert into t1 values (2,2)
*
ERROR at line 1:
ORA-00001: unique constraint (NIALL.SYS_C0025839) violated

SQL> insert into t1 values (3,2);

1 row created.

SQL> insert into t2 values(1,1);

1 row created.

SQL> insert into t2 values(3,2);

1 row created.

SQL> commit;

Commit complete.

SQL> delete t1
  2 where exists (
  3 select null from t2
  4 where t1.id=t2.id
  5 and t1.col2=t2.col2);

2 rows deleted.

SQL> select * from t1;

        ID COL2
---------- ----------

         2 1

SQL> spoo off Received on Thu Jul 17 2003 - 10:16:33 CDT