Résolu - Effacer des champs dans des tables à partir d'un select

Résolu - Effacer des champs dans des tables à partir d'un select - SQL/NoSQL - Programmation

Marsh Posté le 08-11-2006 à 16:26:32    

Bonjour,
 
Sous postgresql, je dispose de plusieurs tables (toto, titi, tutu) qui ont un champ id_doc en commun.
(toto: id_doc + machin, titi: id_doc + truc, tutu: id_doc + bidule)
 
Dans la table toto, le champ 'machin' est parfois vide.
Je sais lister les enregistrements concernés :
SELECT id_doc FROM toto WHERE machin='';
 
Je veux supprimer tous les enregistrements de mes 3 tables dont le contenu du champ id_doc apparaît en résultat de cette requête.
 
Je veux bien faire 3 requêtes pour traiter séparément mes 3 tables, mais comme j'ignore combien j'ai de réponses, il me faut des instructions qui diraient :
1 - Pour_toutes_les_reponses dans (SELECT id_doc FROM toto WHERE machin='';) supprime l'enregistrement correspondant dans tutu;
2 - Fais_pareil dans titi;
3 - Pendant_que_tu_es fais_pareil dans toto;
 
Ca doit être possible, j'ai même peut-être su le faire, mais mes notions SQL sont parties assez loin.
 
Je tape les commandes directement sous psql, mais je veux bien d'un script à lancer sous linux pour automatiser tout cela.
 
Merci d'avance pour le coup de main.


Message édité par Kiosquec le 28-11-2006 à 10:08:18
Reply

Marsh Posté le 08-11-2006 à 16:26:32   

Reply

Marsh Posté le 08-11-2006 à 17:14:31    

J'ai essayé ça :
DELETE FROM titi WHERE id_doc=(SELECT titi.id_doc FROM titi i JOIN toto o ON i.id_doc=o.id_doc AND o.machin='');
 
Ca m'annonce 0 effacement. Peut-être tout bêtement parce que dans l'état actuel de mes tables le cas ne se présente pas !
 
Si je fais :
SELECT id_doc FROM titi WHERE id_doc=(SELECT titi.id_doc FROM titi i JOIN toto o ON i.id_doc=o.id_doc AND o.machin='');
j'obtiens en effet 0 résutat.
 
Alors voilà, problème peut-être résolu, mais je ne peux pas le vérifier.
Mes requêtes vous paraissent-elles correctes ?
 

Reply

Marsh Posté le 08-11-2006 à 20:25:23    

oui

Reply

Marsh Posté le 08-11-2006 à 22:31:41    

Je ne connais pas la structure en postgres .. mais moi lorsque je fait un where sur un select je dois faire Where id_doc IN (Select ....)

Reply

Marsh Posté le 09-11-2006 à 05:53:16    

Pourquoi faire une jointure et ne pas utiliser tout bêtement la requête que tu cites en première :??:
 
Sinon pour la suppression en effet t'auras pas le choix faut le faire à la main table par table à moins d'avoir un mcd avec des contraintes (clés étrangères) et d'utiliser une syntaxe style delete on cascade...

Message cité 1 fois
Reply

Marsh Posté le 09-11-2006 à 10:52:21    

leflos5 a écrit :

Pourquoi faire une jointure et ne pas utiliser tout bêtement la requête que tu cites en première :??:
 
Sinon pour la suppression en effet t'auras pas le choix faut le faire à la main table par table à moins d'avoir un mcd avec des contraintes (clés étrangères) et d'utiliser une syntaxe style delete on cascade...


 
Je ne peux pas utiliser la première requête car le critère qui décide de l'effacement de la ligne n'existe que dans une table et pas dans les autres.
Si ce n'est pas clairement exprimé, je dois effacer des lignes dans une table chaque fois que sa valeur id_doc est associée à un champ machin vide dans une autre table.
 
Pour arranger le tout, je viens de constater que dans une de mes tables la même valeur id_doc apparaît à plusieurs reprises. Le résultat, c'est que pour cette table-ci ma formule avec jointures donne un résultat farfelu.
 
DELETE ON CASCADE ? Je vais voir de quoi il s'agit, je ne connais pas encore.
 
Pour WHERE id_doc IN (SELECT ...), j'essaie ceci :
 
SELECT * FROM tutu WHERE id_doc IN (SELECT id_doc FROM toto WHERE machin='');
 
Sachant que dans tutu j'ai plusieurs fois la même valeur pour id_doc, le résultat a l'air beaucoup moins farfelu que dans ma formulation avec jointure. Pour les autres tables, où id_doc a chaque fois des valeurs différentes, j'obtiens le même résultat qu'avec la jointure.
Ca m'a l'air pas mal, donc.
 
Je propose donc comme solution ces trois instructions successives :
DELETE FROM tutu WHERE id_doc IN (SELECT id_doc FROM toto WHERE machin='');
DELETE FROM titi WHERE id_doc IN (SELECT id_doc FROM toto WHERE machin='');
DELETE FROM toto WHERE machin='';
 
Remarque, DELETE FROM toto WHERE id_doc IN (SELECT id_doc FROM toto WHERE machin=''); c'est bourrin mais ça devrait marcher aussi, donc s'il y a un moyen réunir les trois commandes en une seule...


Message édité par Kiosquec le 09-11-2006 à 10:53:32
Reply

Marsh Posté le 09-11-2006 à 15:27:10    

Non pas moyen de réunir en un, le delete concerne une seule table qu'il faut indiquer :spamafote:

Message cité 1 fois
Reply

Marsh Posté le 09-11-2006 à 15:29:00    

IN :sweat:
 
EXIST :o
 

delete titi where exists (select null from toto where toto.doc_id = titi.doc_id and toto.machin = '')


 
Je vois encore un IN et c'est seau d'eau gravier :o

Reply

Marsh Posté le 09-11-2006 à 15:32:18    

leflos5 a écrit :

Non pas moyen de réunir en un, le delete concerne une seule table qu'il faut indiquer :spamafote:


 
si, y'a moyen.
 


create view mavuequivabien
as
select toto.id, toto.machin
from toto inner join titi on toto.doc_id = titi.doc_id inner join tutu on titi.doc_id = tutu.doc_id;
 
delete mavuequivabien
where machin = '';


 
Sous PostGre, ça devrait marcher.
Sinon, plus simple : créer un trigger sur TOTO, "before delete" qui effectue le DELETE sur tutu et le DELETE sur titi pour chaque enregistrement de "old" où machin = ''.
Il suffit ensuite de faire le delete sur TOTO.

Reply

Marsh Posté le 09-11-2006 à 15:53:28    

Oublie pour le coup de la vue, en fait il faudra faire un trigger dessus (du moins, SQL Server en nécessite un).
 
Voici un exemple complet avec les "exists", ou avec un trigger.
Perso, je préconise fortement le coup du trigger.
 


create table toto (id numeric(18,0) primary key, machin varchar(50));
create table titi (id numeric(18,0) primary key, machin varchar(50));
create table tutu (id numeric(18,0) primary key, machin varchar(50));
go
 
begin tran;
 
insert into toto (id, machin) values (1, 'test1');
insert into toto (id, machin) values (2, '');
insert into toto (id, machin) values (3, '');
insert into toto (id, machin) values (4, 'test4');
insert into toto (id, machin) values (5, 'test5');
 
insert into titi (id, machin) values (1, 'test1');
insert into titi (id, machin) values (2, 'test2');
insert into titi (id, machin) values (3, 'test3');
insert into titi (id, machin) values (4, 'test4');
insert into titi (id, machin) values (5, 'test5');
 
insert into tutu (id, machin) values (1, 'test1');
insert into tutu (id, machin) values (2, 'test2');
insert into tutu (id, machin) values (3, 'test3');
insert into tutu (id, machin) values (4, 'test4');
insert into tutu (id, machin) values (5, 'test5');
 
commit;
 
begin tran;
 
select 'Exemple 1'
delete titi where exists (select null from toto where toto.id = titi.id and toto.machin = '');
 
select * from titi;
 
rollback;
 
select 'Exemple 2';
go
 
create trigger toto_del
on toto
after delete
as
begin
   declare @id numeric(18,0);
   declare @machin varchar(50);
 
   DECLARE delete_cursor CURSOR LOCAL FOR  
   SELECT id, machin from deleted;
 
   OPEN delete_cursor;
 
   FETCH NEXT FROM delete_cursor  
   INTO @id, @machin;
 
   WHILE @@FETCH_STATUS = 0
   BEGIN
      if @machin = ''
      begin
         delete titi where id = @id;
         delete tutu where id = @id;
      end;
 
      FETCH NEXT FROM delete_cursor  
      INTO @id, @machin;
   END
 
   CLOSE delete_cursor;
   DEALLOCATE delete_cursor;
end
go
 
begin tran;
 
delete toto where machin = '';
 
select * from titi;
 
rollback;
go
 
drop trigger toto_del;
 
drop table tutu;
drop table titi;
drop table toto;
go


 
Sortie :
 


 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
---------
Exemple 1
 
(1 row(s) affected)
 
(2 row(s) affected)
 
id                                      machin
--------------------------------------- --------------------------------------------------
1                                       test1
4                                       test4
5                                       test5
 
(3 row(s) affected)
 
 
---------
Exemple 2
 
(1 row(s) affected)
 
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(2 row(s) affected)
id                                      machin
--------------------------------------- --------------------------------------------------
1                                       test1
4                                       test4
5                                       test5
 
(3 row(s) affected)

Reply

Marsh Posté le 09-11-2006 à 15:53:28   

Reply

Marsh Posté le 09-11-2006 à 15:56:14    

ps : le trigger sur le delete peut se passer de curseur et être amplement plus rapide si on lance ces requêtes (à la place de tout son contenu actuel).
Là c'est juste pour que son comportement soit plus explicite.
 


delete titi where exists (select null from deleted where deleted.id = titi.id and deleted.machin = '');
delete tutu where exists (select null from deleted where deleted.id = tutu.id and deleted.machin = '');

Reply

Marsh Posté le 10-11-2006 à 14:56:31    

Ca va tu t'amuses bien tout seul :whistle:
 
Si tu le dis, mais je sais pas pourquoi, quand on me parle de requête sql, je pense pas aux déclencheurs et encore moins à écrire tout un blabla alors que la question semble orientée pour un nettoyage de table :o
 
Je serais bien tenté de proposer une version php qui aurait juste une petite boucle pour éxécuter les 3 delete par id :whistle:

Reply

Marsh Posté le 11-11-2006 à 01:51:43    

après, t'en pense ce que tu en veux. ne pas penser déclencheur quand on te parle de règle, c'est déjà une erreur importante, liée à tous les teubés qui ont appris à développer sur MySQL.
 
on en arrive à un point où tout le monde utilise des briques pourries écrite par 25 personnes en PHP et que personne ne comprend, afin de valider la cohérence de données critiques dans une base MySQL même pas configurée pour faire des transactions.
 
moi chuis un vieux de la veille, et ce genre de choses me fait vomir. en tout cas, je refuse purement et simplement de faire confiance à un programme PHP qui fait des boucles pour exécuter des requêtes faisant des "in". je préfère encore passer pour un fou intégriste avec mes déclencheurs, et garantir une cohérence absolue des données de la base.

Reply

Marsh Posté le 11-11-2006 à 08:55:25    

Mais tu as raison, c'est juste pas naturel pour faire du ménage une fois :) Et en effet mysql n'a pas aidé à penser sgbd de part sa facilité d'utilisation pour le web, résultat en effet tout le monde fait du code moisi parce que tout le monde n'est pas non plus DBA :spamafote:

Reply

Marsh Posté le 13-11-2006 à 00:18:36    

bah c'est pas trop un problème de DBA ou non.
 
notamment, si dans un mode "projet", en effet seul un DBA mettra en place les trigger, il n'en reste pas moins que c'est à l'analyste de les penser, et aux dev de les écrire. le dba se contente de valider qu'ils ne vont pas mettre en périle les données (triggers récursifs, etc.), ainsi que vérifier que les index actuels et la configuration des tables offriront des performances optimales avec l'utilisation du trigger.
 
mais dans tous les cas, et en ça, mysql est fortement coupable, l'écriture de procédures stockées et de triggers fait partie entière de l'écriture d'un logiciel, et ne demande pas de compétences DBA.
 
on peut très bien s'en passer, mais quand un sgbd offre la possibilité de les utiliser, je suis trouve très domage de ne pas les utiliser, car plus performants, et garantissant une intégrité optimale : on n'a plus besoin de remettre les contrôles dans chaque brique logicielle, tout est centralisé dans la base et obligatoirement exécuté à chaque requête.
 
ensuite, si en effet il s'agit d'un script "one shot" de nettoyage, ce n'est pas forcément utile de faire un trigger. mais encore moins de faire une brique PHP. SQL (PL/SQL, T-SQL ou PG/SQL) offre tous les outils nécessaires pour faire de genre de scripts, parfaitement autonomes, sans besoin d'utiliser de PHP. mon "exemple 1" le montre d'ailleurs parfaitement, puisqu'il fait le nettoyage en une seule bête requête (enfin, deux pour nettoyer les deux tables).

Reply

Marsh Posté le 15-11-2006 à 15:02:04    

Le nettoyage doit se faire périodiquement, donc même si je n'ai pas encore compris (je vais voir ça à tête reposée), la solution de MagicBuzz a l'air plus prometteuse.
 
Maintenant (pas sur la tête !), c'est quoi le problème avec IN ?
 
(On avait dit : pas sur la tête !) [:kiosquec]

Reply

Marsh Posté le 15-11-2006 à 15:09:36    

ben le problème de IN, c'est que c'est extrêment lent, car aucun mécanisme de substitution ou optimisation n'est utilisable par le sgbd pour l'améliorer : il doit faire la sous-requête, créer un jeu de résultats entier (ça peut rapidement être des milliers de lignes) et boucler, pour chaque ligne de la requête principale, sur le résultat du IN.
alors qu'un EXISTS ne fait qu'une bête jointure, infiniment plus rapide, le IN est une horreur.
 
pour du "one shot", c'est pas trop grave (mise à part que ça plante quand y'a trop de lignes)

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed