Comment utiliser la mise à jour, supprimer et insérer des instructions SQL

En plus de SELECT déclarations, METTRE À JOUR, EFFACER, et INSERT Les instructions SQL peuvent également inclure OÙ clauses. Ceux OÙ clauses peuvent contenir des sous-requêtes de la même manière que SELECT DÉCLARATIONSOÙ clauses font.

Par exemple, Zetec a juste fait un volume accord d'achat avec des ventes olympiques et veut fournir olympique avec un crédit rétroactif de 10 pour cent pour l'ensemble de ses achats dans le dernier mois. Vous pouvez donner ce crédit avec un METTRE À JOUR déclaration:

UPDATE TRANSMASTERSET NetAmount = NetAmount * 0.9WHERE SaleDate> (CurrentDate - 30) JOUR ET CustID = (SELECT CustIDFROM CUSTOMERWHERE Société = 'Olympique Sales') -

Vous pouvez aussi avoir une sous-requête corrélée dans un METTRE À JOUR déclaration. Supposons que la table de la clientèle a une colonne LastMonthsMax, Zetec et veut donner un tel crédit pour les achats qui dépassent LastMonthsMax pour le client:

UPDATE TRANSMASTER TMSET NetAmount = NetAmount * 0.9WHERE NetAmount> (SELECT CLIENT LastMonthsMaxFROM CDes C.CustID = TM.CustID) -

Notez que cette sous-requête est corrélée: Le OÙ clause dans les dernières références de ligne à la fois le CustID de la ligne de client à partir de la sous-requête et de la CustID de la rangée de TRANSMASTER courant qui est un candidat pour la mise à jour.

Une sous-requête dans une METTRE À JOUR déclaration peut également faire référence à la table qui est mis à jour. Supposons que Zetec veut donner un crédit de 10 pour cent pour les clients dont les achats ont dépassé 10 000 $:

UPDATE TRANSMASTER TM1SET NetAmount = NetAmount * 0.9WHERE 10000 lt; (SELECT SUM (NetAmount) DE TRANSMASTER TM2WHERE TM1.CustID = TM2.CustID) -

La sous-requête interne calcule la SOMME du Montant net colonne pour toutes les lignes de TRANSMASTER pour le même client. Qu'est-ce que cela signifie? Supposons que le client CustID = 37 dispose de quatre lignes dans TRANSMASTER avec des valeurs pour Montant net: 3000, 5000, 2000, et 1000. La SOMME de Montant net pour ça CustID est 11000.

L'ordre dans lequel le METTRE À JOUR déclaration traite les lignes est défini par votre mise en œuvre et est généralement pas prévisible. L'ordre peut différer selon la façon dont les rangées sont disposées sur le disque. Supposons que la mise en œuvre traite les rangs pour cette CustID dans cet ordre: d'abord le TRANSMASTER avec un Montant net de 3000, puis celui avec Montant net= 5000, et ainsi de suite.

Après les trois premières rangées pour CustID 37 ont été mis à jour, leur Montant net les valeurs sont 2700 (90 pour cent de 3000 $), 4500 (90 pour cent de 5000 $), et 1,800 (90 pour cent de 2000 $). Ensuite, lorsque vous traitez la dernière rangée de TRANSMASTER pour CustID 37 (dont Montant net est 1000), la SOMME renvoyée par la sous-requête serait paraître être 10000, et l'ancien Montant net valeur de la dernière rangée pour CustID 37.

Ainsi, il semblerait que la dernière rangée pour CustID 37 ne sont pas mis à jour, parce que la comparaison avec SOMME est pas vrai - après tout, 10000 est égale ou supérieure 10000. Mais cela ne la façon dont le METTRE À JOUR déclaration est définie lorsqu'un sous-requête fait référence à la table qui est mis à jour.

Tous évaluations des sous-requêtes dans un METTRE À JOUR déclaration de la référence les anciennes valeurs de la table- ceux qui sont mis à jour. Dans ce qui précède METTRE À JOUR pour CustID 37, les sous-requête retourne 11000 - l'original SOMME.

La sous-requête dans une OÙ clause fonctionne comme un SELECT déclaration ou un METTRE À JOUR déclaration. La même chose est vraie pour EFFACER et INSERT. Pour supprimer tous les transactions de Olympiques, utilisez cette déclaration:

DELETE FROM TRANSMASTERWHERE CustID = (SELECT CustIDFROM CUSTOMERWHERE Société = 'Sales olympique') -

Comme avec METTRE À JOUR, EFFACER sous-requêtes peuvent également être corrélées et peuvent également référencer la table étant supprimé. Les règles sont semblables aux règles de METTRE À JOUR les sous-requêtes. Supposons que vous souhaitez supprimer toutes les lignes de TRANSMASTER pour les clients dont le total Montant net est supérieur à 10 000 $:

DELETE FROM TRANSMASTER TM1WHERE 10000 lt; (SELECT SUM (NetAmount) DE TRANSMASTER TM2WHERE TM1.CustID = TM2.CustID) -

Cette requête supprime toutes les lignes de TRANSMASTER qui ont CustID 37, ainsi que tous les autres clients avec des achats de plus de 10.000 $. Toutes les références à TRANSMASTER dans la sous-requête désignent le contenu de TRANSMASTER avant que les suppressions de l'instruction en cours. Donc, même si vous supprimez la dernière ligne pour TRANSMASTER CustID 37, la sous-requête est évaluée sur la table et les retours de TRANSMASTER originale 11000.

Lorsque vous mettez à jour, supprimer ou insérer des enregistrements de base de données, vous risquez de rendre les données d'une table incompatible avec les autres tables de la base de données. Une telle incohérence est appelé modification anomaly. Si vous supprimez des enregistrements de TRANSMASTER et une table de TRANSDETAIL dépend TRANSMASTER, vous devez supprimer les enregistrements correspondants de TRANSDETAIL, aussi.

Cette opération est appelée une suppression en cascade, parce que la suppression d'un enregistrement parent doit en cascade à ses enregistrements enfants associés. Sinon, les enregistrements enfants deviennent orphelins non supprimés. Dans ce cas, ils seraient les lignes de détail factures qui sont dans les limbes parce qu'ils ne sont plus connectés à un enregistrement de la facture.

Si votre implémentation de SQL ne supporte pas les suppressions en cascade, vous devez le faire vous-même les suppressions. Dans ce cas, supprimez les dossiers appropriés de la table enfant avant de supprimer l'enregistrement correspondant du parent. De cette façon, vous ne devez pas enregistrements orphelins dans la table enfant, même pour une seconde.