suppression et jointure

Bonjour à tous,

Voici mon problème SQL :
J'ai deux tables plus une table d'association
TABLE1
------
T1_ID

Association
-----------
T1_ID
T2_ID

TABLE2
------
T2_ID
TYPE_OBJ

Et une requête de suppression du style : "Supprimer tous les objets Table1 associés à un objet Table2 de type 'truc' ".

Ce qui donnerait :

Code:

---

1 2 3 4 5 6 7

DELETE FROM Table1 WHERE Table1.T1_ID IN ( SELECT Table1.T1_ID FROM Table1 INNER JOIN Association ON Association.T1_ID = Table1.T1_ID INNER JOIN Table2 ON Table2.T2_ID = Association.T2_ID WHERE Table2.Type_Obj = 'truc' )

---

Sauf que ça va pas marcher. En effet, on ne peut pas supprimer les objets Table1 sans perdre l'intégrité de la base parce qu'ils sont encore référencés dans la table Association.
Et si je supprime d'abord les lignes de la table Association qui référencent les objets Table1 qu'on veut supprimer, ma première requête ne marchera plus (impossible de retrouver les objets Table1 associés à des objets Table2 de type 'truc' vu que... les association auront été supprimées).


Comme je sens qu'il y a des petits malins dans l'assistance je vois arriver la solution : "Tu mets un ON DELETE CASCADE sur tes clés étrangères".
Ouais.
Sauf que.
On était jeunes, on était fous et on a pas mis de contraintes quand on a créé la base :aie:. Dommage parce qu'actuellement on a pas forcément le droit de retoucher à la structure de la base.
Donc il me faut une autre solution. D'avance merci ;)

Base de données : Mysql 5

Bonjour,

Pourquoi ne pas faire 3 delete:

Premier delete pour la table1, second pour la table des associations, et le dernier sur la table 2 ?

Citation:

---

En effet, on ne peut pas supprimer les objets Table1 sans perdre l'intégrité de la base parce qu'ils sont encore référencés dans la table Association.

---

Pourquoi, vu qu'il n'y a pas de contraintes de clef ?

Le plus simple est d'utiliser une table temporaire dans laquelle vous stockées les valeurs liées de table 1
Vous commencez par supprimer les lignes de la jointure
Puis vous supprimez les lignes de T1 en correspondance dans la table temporaire.

Avec SQL Server ceci se fait à l'aide d'une clause OUTPUT qui permet d'obtenir après exécution d'un ordre SQL, les lignes impactées.

Par exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

DECLARE @T TABLE (T1_ID);   DELETE FROM ASSOCIATION WHERE T2_ID IN (SELECT T2_ID FROM T2 WHERE T2_OBJET = 'truc') OUPUT deleted.T1_ID INTO @t ;   DELETE FROM T1 WHERE T1_ID IN (SELECT T1_ID FROM @T);

---

Bien entendu vous pouvez aussi faire cela dans un trigger.

Enfin, les procédures stockées sont faites pour cela, et notamment pour pouvoir transactionner ce double DELETE !

A +

Résolution en cinq étapes :
1 - Créez une table avec une colonne T1_ID
2 - Stockez-y vos T1_ID à supprimer
3 - Effacez-les de la table d'association
4 - Effacez-les de la table T1
5 - Droppez la table temporaire

Je suis pas sûr que la clause OUTPUT existe en MySql.

Y a-t-il un autre moyen de stocker la liste des identifiants "a supprimer" dans la table temporaire T1 ?

Edit pour Anthony : Je me suis mal exprimé. Il y a des contraintes de clé étrangère, mais elles n'ont pas la caractéristique ON DELETE CASCADE.

Bonjour,

Voici la solution qui me paraît la plus simple (mais j'ai eu les mêmes états d'âme avant de la trouver...)

Code:

---

1 2 3

delete t1 from table1 t1, table2 t2 where t2.type_ob='valeur_type_obj' and t2.id=t1.id ;

---

L'astuce est donc d'utiliser les alias et de le répéter pour la table supprimée entre DELETE et FROM.

La syntaxe est simple, et cela fonctionne également pour supprimer d'un coup les lignes des 2 tables, du genre :

Code:

---

1 2 3

delete t1,t2 from table1 t1, table2 t2 where t2.type_ob='valeur_type_obj' and t2.id=t1.id ;

---

On peut utiliser les divers JOIN si on préfère mais cela ne change rien à ce principe de l'alias entre DELETE et FROM.

Salut !

Presque bien joué, effectivement, mais si j'ai bien compris, il y a un hic :

Citation:

---

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

---

[EDIT]
Et en cherchant rapidement, j'ai pas trouvé de hint en MySQL pour forcer l'ordre de jointure...

Il y a peut-être des cas particuliers où ça ne fonctionnera pas d'après tes recherches, mais pour le cas de base de 2 tables seulement avec jointure et pour supprimer soit l'une soit les deux, je l'utilise sans problèmes...

Citation:

---

Envoyé par pacmann

Salut !

Presque bien joué, effectivement, mais si j'ai bien compris, il y a un hic :



[EDIT]
Et en cherchant rapidement, j'ai pas trouvé de hint en MySQL pour forcer l'ordre de jointure...

---

Avec des FK ?

Oui ça peut marcher. Et ça peut aussi planter.
Bien sûr, on peut y aller à la confiance et à la chance... mais c'est pas fantastique quand même.

Avec des clés étrangères, le mieux c'est quand même ON DELETE CASCADE !