Hello members,
No sorry, this is not about key preservation.
Instead, I have two tables T1 and T2. I want to delete rows from T1 where primary key is also in T2.
I thought that if I select only from T1 then I would delete from T1. But as it turns out it is the first table mentioned in FROM clause that gets deleted from.
SQL> create table t1 (x number primary key, c1 varchar2(1));
Table created.
SQL> create table t2 (x number primary key, c2 varchar2(1));
Table created.
SQL>
SQL> insert into t1 values (1,'a');
1 row created.
SQL> insert into t1 values (2,'b');
1 row created.
SQL> insert into t1 values (3,'c');
1 row created.
SQL> insert into t2 values (1,'X');
1 row created.
SQL> commit
2 /
Commit complete.
SQL>
SQL> delete (select t1.x from T2, t1 where t2.x = t1.x);
1 row deleted.
SQL> select * from t1 union all select * from t2;
X C
---------- -
1 a
2 b
3 c
SQL> rollback
2 /
Rollback complete.
SQL>
SQL> delete (select t1.x from T1, t2 where t2.x = t1.x);
1 row deleted.
SQL> select * from t1 union all select * from t2;
X C
---------- -
2 b
3 c
1 X
SQL> rollback
2 /
Rollback complete.
SQL>
SQL>
SQL> select * from v$version where rownum = 1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
SQL>
Maybe I was home sick when Ms. Teacher covered this, maybe I just wasn't paying attention.
Or something else? - Are there any logic and/or rules involved here, that I never heard of?
Best regards
Peter
Edit:
Seems that the select list has nothing to do with it, at all.
This will delete from T2
delete (select null from T2, t1 where t2.x = t1.x);
And this will delete from T1:
delete (select null from T1, t2 where t2.x = t1.x);
Edited by: Peter on Apr 24, 2012 6:01 AM