delete sur plusieurs tables

Version imprimable

22/03/2006, 11h54
drinkmilk

delete sur plusieurs tables
Bonjour,

comment supprimer des lignes de plusieurs tables à la fois ?

la syntaxe suivante qui me paraissait logique ne passe pas sous Oracle (ORA-00933: La commande SQL ne se termine pas correctement)
Code:

1 2 delete from table1 T1, table2 T2, table3 T3 where (T1.id <>1000) and (T1.ID = T2.ID) and (T1.ID = T3.ID)
Merci
22/03/2006, 12h05
LeoAnderson

cela ne fonctionne pas, il faut faire 3 delete.
22/03/2006, 12h15
drinkmilk

Ok, j'imagine que c'est pour des raisons de sécurité.

Dans mon cas, il faudra plutôt faire une procédure stockée car il y un lien entre les 3 tables.

Merci
22/03/2006, 12h24
laurentschneider
en fait c'est possible d'effacer des lignes de plusieurs tables dans certaines circonstances, mais je le déconseille car on ne voit pas trop ce qui se passe...
Code:

1 2 3 4 delete from (select * from table1 T1, table2 T2, table3 T3 where (T1.id <>1000) and (T1.ID = T2.ID) and (T1.ID = T3.ID));
mais ça ne marche que lorsqu'il y a des contraintes d'intégrité référentielle.
22/03/2006, 14h29
Magnus
Citation:
Envoyé par laurentschneider

en fait c'est possible d'effacer des lignes de plusieurs tables dans certaines circonstances, mais je le déconseille car on ne voit pas trop ce qui se passe...

Code:

1 2 3 4 delete from (select * from table1 T1, table2 T2, table3 T3 where (T1.id <>1000) and (T1.ID = T2.ID) and (T1.ID = T3.ID));

mais ça ne marche que lorsqu'il y a des contraintes d'intégrité référentielle.
Votre proposition est-elle valable en 8i car j'obtiens l'erreur suivante :
Code:

1 2 3 4 delete from ( * ERROR à la ligne 1 : ORA-01752: cannot delete from view without exactly one key-preserved table
22/03/2006, 14h54
LeoAnderson

Citation:

Envoyé par drinkmilk

Ok, j'imagine que c'est pour des raisons de sécurité.

Dans mon cas, il faudra plutôt faire une procédure stockée car il y un lien entre les 3 tables.

Merci

Dans ce cas, s'il y a un lien, pourquoi ne pas avoir la clause ON DELETE CASCADE sur la foreign key ?
22/03/2006, 15h32
laurentschneider
Citation:
Envoyé par Magnus

Citation:

Envoyé par laurentschneider

en fait c'est possible d'effacer des lignes de plusieurs tables dans certaines circonstances, mais je le déconseille car on ne voit pas trop ce qui se passe...

Code:

1 2 3 4 delete from (select * from table1 T1, table2 T2, table3 T3 where (T1.id <>1000) and (T1.ID = T2.ID) and (T1.ID = T3.ID));

mais ça ne marche que lorsqu'il y a des contraintes d'intégrité référentielle.

Votre proposition est-elle valable en 8i car j'obtiens l'erreur suivante :

Code:

1 2 3 4 delete from ( * ERROR à la ligne 1 : ORA-01752: cannot delete from view without exactly one key-preserved table
en 8i c'est possible, mais je pense que ton modèle de données n'est pas ok.

avec emp et dept, ca marche bien

on a

EMP(DEPTNO) references DEPT(DEPTNO)
Code:

1 2 3 SQL> delete (select * from emp,dept where emp.deptno=dept.deptno); 14 rows deleted.
mon conseil est de suivre la première réponse de Leo et d'effectuer 3 delete.

note que le résultat n'est pas forcément celui escompté :-(

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SQL> select (select count(*) from emp), (select count(*) from dept) from dual;                                                                                                  
(SELECTCOUNT(*)FROMEMP) (SELECTCOUNT(*)FROMDEPT)
----------------------- ------------------------
                     14                        4
 
SQL> delete (select * from emp,dept where emp.deptno=dept.deptno);                                                                                                              
 
14 rows deleted.
 
SQL> select (select count(*) from emp), (select count(*) from dept) from dual;                                                                                                  
(SELECTCOUNT(*)FROMEMP) (SELECTCOUNT(*)FROMDEPT)
----------------------- ------------------------
                      0                        4

rien n'a été effacé dans dept.

faire 3 delete me parait vraiment plus approprié

22/03/2006, 15h49
LeoAnderson

Citation:

Envoyé par laurentschneider

rien n'a été effacé dans dept.

faire 3 delete me parait vraiment plus approprié

le plus clean étant quand même d'avoir des FK et du DELETE CASCADE... ;-)

on est sûr de n'oublier aucune table et on n'a qu'une seule instruction...

Citation:

Envoyé par laurentschneider

note que le résultat n'est pas forcément celui escompté :-(

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SQL> select (select count(*) from emp), (select count(*) from dept) from dual;                                                                                                  
(SELECTCOUNT(*)FROMEMP) (SELECTCOUNT(*)FROMDEPT)
----------------------- ------------------------
                     14                        4
 
SQL> delete (select * from emp,dept where emp.deptno=dept.deptno);                                                                                                              
 
14 rows deleted.
 
SQL> select (select count(*) from emp), (select count(*) from dept) from dual;                                                                                                  
(SELECTCOUNT(*)FROMEMP) (SELECTCOUNT(*)FROMDEPT)
----------------------- ------------------------
                      0                        4

rien n'a été effacé dans dept.

faire 3 delete me parait vraiment plus approprié

En 10GR1 ça fonctionne mais comme vous le montrez cette instruction supprime apparemment (?) uniquement les données de la table contenant la foreign key.

Pour info, en 8i j'ai bien respecté les contraintes de primary / foreign key mais franchement j'ai du mal à comprendre l'intérêt d'une telle syntaxe surtout quand on voit ce qu'elle donne à l'exécution...

Citation:

Envoyé par Magnus

mais franchement j'ai du mal à comprendre l'intérêt d'une telle syntaxe surtout quand on voit ce qu'elle donne à l'exécution...

l'interêt est plus évident lorsque tu employes des vues, par exemple GUGUS peut lire et effacer emp, mais pas voir les salaires
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SQL> create or replace view scott.smallemp as select empno, ename, dname, job from scott.emp,scott.dept where emp.deptno=dept.deptno; View created. SQL> grant select,insert,update,delete on scott.smallemp to gugus; Grant succeeded. SQL> connect gugus/gugus Connected. SQL> select * from scott.smallemp where ename='SCOTT'; EMPNO ENAME DNAME JOB ---------- ---------- -------------- --------- 7788 SCOTT RESEARCH ANALYST SQL> delete from scott.smallemp where ename='SCOTT'; 1 row deleted.
Tout de bon
Laurent

Citation:

Envoyé par LeoAnderson

le plus clean étant quand même d'avoir des FK et du DELETE CASCADE... ;-)

c'est bien sûr une question de goût, ce qui me dérange dans le delete cascade, c'est qu'on ne se rend pas compte de ce qu'on efface
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 SQL> create table t1(x number primary key, text varchar2(40)); Table created. SQL> create table t2(y number primary key,x number references t1(x) on delete cascade, text varchar2(40)); Table created. SQL> insert into t1 values (1,'jambon'); 1 row created. SQL> insert into t2 values (1,1,'fromage'); 1 row created. SQL> insert into t2 values (2,1,'moutarde'); 1 row created. SQL> delete t1; 1 row deleted.
en fait, "1 row deleted" est troublant, car en fait, on a effacé 3 lignes, mais c'est sûrement une question d'habitude :roll: