requete DELETE multi-tables

Bonjour à tous,

Je cherche à faire une requête de suppression multi-tables avec Oracle 10g.

Je voudrais supprimer tous les éléments retournés par la requête suivante :

Code:

---

1 2	SELECT * FROM TAB_ARA, TAB_VIR WHERE TAB_ARA.ARA_NUM_COMPTE_CLI = TAB_VIR.VIR_NUM_COMPTE_EMET;

---

j'ai donc naturellement lancé :

Code:

---

1 2	DELETE FROM TAB_ARA, TAB_VIR WHERE TAB_ARA.ARA_NUM_COMPTE_CLI = TAB_VIR.VIR_NUM_COMPTE_EMET);

---

mais ca ne marche pas :

Citation:

---

DELETE FROM TAB_ARA, TAB_VIR
...............................*
ERROR at line 1:
ORA-00933: SQL command not properly ended

---

du coup, j'ai essayé de faire une requête imbriquée :

Code:

---

1 2 3

DELETE FROM ( SELECT * FROM TAB_ARA, TAB_VIR WHERE TAB_ARA.ARA_NUM_COMPTE_CLI = TAB_VIR.VIR_NUM_COMPTE_EMET);

---

et ca renvoie :

Citation:

---

ORA-01752: cannot delete from view without exactly one key-preserved table

---

La page suivant explique comment faire :
http://dev.mysql.com/doc/refman/5.0/fr/delete.html
mais c'est pour mysql. J'ai rien trouvé qui marcherait avec Oracle.

Pouvez-vous m'aider ?

c'est pas possible... le mieux c'est une foreign key avec DELETE CASCADE, quand tu supprimes le père ça supprime les fils ;)

Merci pour la réponse mais il me semble qu'avec une foreign key, on doit avoir une colonne qui contient strictement les mêmes données dans les 2 tables ?

moi je cherche à supprimer les lignes qui sont identiques, pour ne traiter que les cas problématiques.

j'ai fait :

Code:

---

1 2 3

CREATE TABLE test AS ( SELECT * FROM TAB_ARA WHERE rownum<100); CREATE TABLE test2 AS ( SELECT * FROM TAB_VIR WHERE rownum<100); ALTER TABLE test ADD CONSTRAINT unicite FOREIGN KEY (ARA_NUM_COMPTE_CLI) REFERENCES test2(VIR_NUM_COMPTE_EMET);

---

et il me renvoie "no matching unique or primary key for this column-list".
ce qui me parait normal vu que je sais que les 2 colonnes ne sont pas identiques.

Peitit exemple avec le DELETE CASCADE sur une FK, et ca marche très bien :

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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

SQL> CREATE TABLE test_dup AS ( SELECT object_name, object_id, 0 as oid FROM all_objects);   Table created.   SQL> CREATE TABLE test2_dup AS ( SELECT object_name, 0 as object_id, object_id as oid FROM test_dup);   Table created.   SQL> ALTER TABLE test_dup ADD CONSTRAINT unicite_pk PRIMARY KEY (object_id);   Table altered.   SQL> ALTER TABLE test2_dup ADD CONSTRAINT unicite_fk FOREIGN KEY (oid) REFERENCES test_dup(object_id) on delete cascade;   Table altered.   SQL> SQL> select object_id from test_dup where object_id <= 10;   OBJECT_ID ---------- 3 4 5 7 9   SQL> select oid from test2_dup where oid <= 10;   OID ---------- 5 9 3 7 4   SQL> SQL> delete test_dup where object_id <= 10;   5 rows deleted.   SQL> SQL> select object_id from test_dup where object_id <= 10;   no rows selected   SQL> select oid from test2_dup where oid <= 10;   no rows selected   SQL>

---

Nicolas.

Je cherche un moyen de supprimer des éléments qui se trouvent dans les 2 tables et ne garder dans chaque table que les éléments qui ne se trouvent pas dans l'autre.

exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

drop table test; drop table test2; create table test (a number); create table test2 (b number); insert into test values (1); insert into test values (2); insert into test2 values (2); insert into test2 values (3);   ALTER TABLE test ADD CONSTRAINT unicite_pk PRIMARY KEY (a); ALTER TABLE test2 ADD CONSTRAINT unicite_fk FOREIGN KEY (b) REFERENCES test(a) ON DELETE cascade;

---

ORA-02298: cannot validate (BO.UNICITE_FK) - parent keys not found

si j'insère les mêmes valeurs dans les 2 tables, la contrainte se crée.

je croit que je vais regarder du coté là : http://oracle.developpez.com/faq/?pa...#errconstraint

mais ca me parait quand meme bien compliqué ...

Merci pour votre aide.

un moyen pour faire ça est d'ajouter une foreign key sur les 2 tables et de tester les rejets de contraintes:

1 - création de la table des exceptions :

Code:

---

create table exceptions(row_id rowid, owner varchar2(30), table_name varchar2(30), constraint varchar2(30));

---

2 - contraintes croisées :

Code:

---

1 2 3 4 5

ALTER TABLE test ADD CONSTRAINT unicite_pk PRIMARY KEY (a); ALTER TABLE test2 ADD CONSTRAINT unicite2_pk PRIMARY KEY (b); ALTER TABLE test ADD CONSTRAINT unicite_fk FOREIGN KEY (a) REFERENCES test2(b) exceptions into exceptions; ALTER TABLE test2 ADD CONSTRAINT unicite2_fk FOREIGN KEY (b) REFERENCES test(a) exceptions into exceptions;

---

3 - extraction depuis la table des exceptions :

Code:

---

1 2 3

select * from test where rowid in ( select row_id from exceptions where table_name='TEST'); select * from test2 where rowid in ( select row_id from exceptions where table_name='TEST2');

---

Je teste tout de suite sur la vraie base.

ORA-02437: cannot validate (BO.VIR_PK) - primary key violated

les colonnes ARA_NUM_COMPTE_CLI et VIR_NUM_COMPTE_EMET ne peuvent pas être déclarées en tant que clés uniques.
ca devient compliqué là.

Citation:

---

Envoyé par Diwann

supprimer des éléments qui se trouvent dans les 2 tables et ne garder dans chaque table que les éléments qui ne se trouvent pas dans l'autre.

---

Je ne sais pas si tu pourras faire çà en une requête (est-ce vraiment nécessaire ?), mais avec un tout petit PL/SQL, çà peut être beaucoup plus simple (à voir tout de même pour les perf.) :

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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

SQL> CREATE TABLE test (a number);   Table created.   SQL> CREATE TABLE test2 (b number);   Table created.   SQL> INSERT INTO test VALUES (1);   1 row created.   SQL> INSERT INTO test VALUES (2);   1 row created.   SQL> INSERT INTO test2 VALUES (2);   1 row created.   SQL> INSERT INTO test2 VALUES (3);   1 row created.   SQL> SQL> begin 2 for x in (select a from test where exists (select 1 from test2 where a=b)) loop 3 delete test where a = x.a; 4 delete test2 where b = x.a; 5 end loop; 6 end; 7 /   PL/SQL procedure successfully completed.   SQL> SQL> select * from test;   A ---------- 1   SQL> select * from test2;   B ---------- 3   SQL>

---

Nicolas.

a ouais !
trop fort.
Merci beaucoup, je teste ca demain.

Ca marche.
Thanx a lot Nicolas.

Je vais tester les perfs maintenant, mais ca devrait aller avec < 100 000 enregistrements.

Je reviens sur ce sujet pour signaler qu'après des tests réels, les perfs se sont révélées déplorables (plusieurs heures parfois pour 150 000 lignes); et pour expliquer comment on a pu réduire ça à quelques secondes.

L'idée étant qu'il y a très peu d'enregistrements différents d'une table à l'autre, et qu'il y a des optimisations possible en diminuant le nombre de requete DELETE effectuées.

on a donc créé une colonne supplémentaire pour indiquer les enregistrements qu'il faudra garder.
On utilise alors le script de NGasparotto pour chercher les enregistrements présents seulement dans l'une des 2 tables en faisant un NOT EXISTS à la place du EXISTS, et à la fin on supprime en une requete tous les éléments qui ont cette colonne vide.

code :

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

drop table test3; drop table test4; CREATE TABLE test3 AS ( SELECT * FROM TAB_ARA); CREATE TABLE test4 AS ( SELECT * FROM TAB_VIR); ALTER TABLE test3 ADD keep INT; ALTER TABLE test4 ADD keep INT;   begin FOR x IN (SELECT * FROM test4 WHERE NOT EXISTS (SELECT 1 FROM test3 WHERE a=b) )loop UPDATE test4 SET keep = 1 WHERE a = x.a; end loop;   FOR x IN (SELECT * FROM test3 WHERE NOT EXISTS (SELECT 1 FROM test4 WHERE a=b) ) loop UPDATE test3 SET keep = 1 WHERE b = x.b; end loop;   DELETE FROM test3 where keep IS NULL; DELETE FROM test4 where keep IS NULL; commit; end; / ALTER TABLE test3 DROP column keep; ALTER TABLE test4 DROP column keep;

---

ce qui nous a permis de passer de 4 heures à 40 sec.