"abhijit" <abhi (AT) nomail (DOT) com> wrote

Won't that potentially delete rows where one field matches but not the
other?

So...

table_1:
field1: 1
field2: 2

field1: 3
field2: 4

And table_2:
field1: 1
field2: 7

field1: 9
field2: 2

I -believe- (happy to be proved wrong...) that your statement above would
match a row as table_1's field1 is in (1, 9) and table_1's field2 is in (7,
2). However, no row -should- be matched as no row in table_2 has a fields
identical to those in table_1.

That not the case?


Cheers,
Ian

"Ian McCall" <ian (AT) eruvia (DOT) org> wrote

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

Niall Litchfield wrote:
I was just about to post an answer with that version of the delete
statement as well :-)

I would think that this is more efficient then having two subselects.

Thomas

"Thomas Kellerer" <spam_eater (AT) gmx (DOT) net> wrote

I *believe* that deleting from a view would be yet more efficient, but
implies that the base tables need primary keys.


--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

or.. an even cuter syntax!

delete from t1 where (col1, col2) in (select col1, col2 from t2);


"Niall Litchfield" <niall.litchfield (AT) dial (DOT) pipex.com> wrote

It's recent.. I know it works in 9i, but not sure about 8i (don't have an 8i
instance to try it on). It absolutely did not work in 7.x.





"Thomas Kellerer" <spam_eater (AT) gmx (DOT) net> wrote

I just tried it with Oracle 8.1.7 and PostgreSQL 7.2.1
It works with both...

Cheers
Thomas

JJ Reynolds schrieb: