Après avoir vu en long, en large et en travers le SELECT, nous allons maintenant nous consacrer aux ordres SQL permettant de mettre à jour les données. Ces ordres sont au nombre de trois : INSERT pour l'insertion de nouvelle(s) ligne(s) dans une table, DELETE pour la suppression et enfin UPDATE qui permet de modifier tout ou partie des valeurs des colonnes.
NOTA : La structure de la base de données exemple, ainsi qu'une version des principales bases utilisées sont disponibles dans la page "La base de données exemple".
Il existe une différence fondamentale entre l'ordre de manipulation des données qu'est le SELECT et les différents ordres de mise à jour de données :
parce qu'en dehors de problèmes de syntaxe ou de typage, le select est une opération dont l'aboutissement est en théorie toujours garantie (sauf à indiquer des noms d'objets inexistants) alors que les opérations de mise à jour des données sont susceptibles d'être rejetées, notamment si elles violent des contraintes
parce qu'autant il est possible d'effectuer des extractions de données via un ordre SELECT sur plusieurs tables, autant il est impossible d'insérer, de modifier ou de supprimer dans plusieurs tables simultanément
parce qu'une requête de mise à jour (INSERT, UPDATE ou SELECT) est une transaction en elle même et que tout doit être réalisé, sinon rien ne se passe (en particulier si une seule donnée viole une contrainte aucune insertion, mise à jour ou supression n'est effective dans le cas d'un ordre concernant la mise à jour de plusieurs lignes simultanément)
parce qu'à moins de gérer une transaction et notamment à l'aide du mot clef ROLLBACK, il est impossible de revenir sur un ordre de mise à jour si l'on s'est trompé. Dans le cas d'un SELECT ceci n'a pas d'importance puisque les données ne sont pas modifiées
1. Insérer à l'aide d'INSERT
Vous trouverez des compléments d'information sur le sujet aux pages 235 à 239 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.
La syntaxe de base de l'ordre SQL d'insertion de données dans une table est la suivante :
la liste des colonnes visées peut être ommise à condition que l'ordre d'insertion concerne toutes les colonnes de la table.
la liste des valeurs peut être remplacée par un constructeur de lignes valuées pour une insertion de plusieurs lignes en un seul ordre, mais rares sont les SGBDR à l'accepter (Oracle est l'un des rares SGBDR à accepter cette syntaxe).
ATTENTION : l'ordre des valeurs de la liste doit être le même que l'ordre des colonnes visées et cela, même si la liste des colonnes visées est ommise.
REMARQUE : Du fait de sa syntaxe, l'ordre INSERT se décompose en trois ordres assez différents :
Insertion simple explicite
Insertion multiple explicite (à l'aide du constructeur de lignes valuées)
Insertion à base de sous requête SELECT
Insertion des valeurs par defaut
Nous allons maintenant détailler ces différentes déclinaisons de l'ordre INSERT...
1.1. Insertion simple explicite
Cette syntaxe porte sur l'insertion d'une ligne unique au sein de la table. Il s'agit de préciser les valeurs à insérer explicitement.
Cet exemple propose d'insérer dans la table T_MODE_PAIEMENT une ligne comportant les valeurs "CB" et "Carte bancaire" dans les colonnes respectives PMT_CODE et PMT_LIBELLE.
Compte tenu que cette table ne possède que deux colonnes, on aurait pu omettre de préciser les colonnes. Dans ce cas, la requête devient :
Cet exemple propose d'insérer dans la table T_TITRE trois lignes de valeurs dans les colonnes TIT_CODE et TIT_LIBELLE.
De même que dans notre précédent exemple, cette table ne possèdant que deux colonnes, on aurait pu omettre de préciser les colonnes. Dans ce cas, la requête devient :
Le constructeur de lignes valuées consiste à donner une liste de ligne en argument.
NOTA : le constructeur de lignes valuées est rarement implémenté dans les SGBDR. Oracle est l'un de seuls à accepter une telle syntaxe.
1.3. Insertion partiellement explicite avec le mot clef DEFAULT
Si la définition de la table possède une ou plusieurs valeurs par défaut, alors il est possible de les y insérer en utilisant le mot clef DEFAUT en lieu et place de la valeur. Supposons que nous créons une table permettant de "pister" les connexions à la base de données, de la manière suivante :
Qui donne encore le même résultat si l'ordre est exécuté au même moment !
1.4. Insertion totalement implicite avec l'expression DEFAULT VALUES
Si chacune des colonnes de la définition de la table possède des valeurs par défaut, on peut demander l'insertion de toutes les valeurs par défaut en utilisant l'expression clef DEFAULT VALUES. Dans ce cas il ne faut pas préciser les noms des colonnes :
Exemple 9
INSERTINTO T_SUIVI_CONNEXION
DEFAULTVALUES
Qui insérera l'utilisateur 'Administrateur' avec la date et l'heure courante.
1.5. Insertion multiple à base de sous requête SELECT
On peut insérer une ou plusieurs lignes dans une table en utilisant une sous requête de type SELECT. Dans ce cas les colonnes retournées par l'ordre SELECT doivent avoir les contraintes suivantes :
être en nombre didentique aux colonnes précisées dans la liste ou en l'absence de précision de cette liste le même nombre de colonnes que la table
avoir le même ordre que l'ordre des noms de colonnes de la liste ou bien le même ordre que les colonnes de la table si l'on omet cette liste
avoir des types correspondant
répondre à toutes les contraintes et dans le cas ou au moins une seule valeur viole une contrainte aucune ligne n'est insérée
Nous allons décrire différents cas et le comportement du SGBDR correspondant. Pour ce faire, je vous propose de créer de toutes pièces une nouvelle table de notre base de données exemple, la table des prospects et d'y insérer explicitement quelques données :
Décomposons le travail du SGBDR... Première étape, exécution de la sous requête SELECT :
SELECT PRP_ID, PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT
PRP_ID PRP_CODE_TITRE PRP_NOM PRP_PRENOM
------- -------------- ---------- ----------
1 M. Dupont Alain
2 Mme. Durand Aline
3 M. Dubois NULL
Seconde étape, Il faut maintenant insérer ces données dans les colonnes CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM correspondantes de la table T_CLIENT. Celle-ci contient :
SELECT CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM
FROM T_CLIENT
CLI_ID TIT_CODE CLI_NOM CLI_PRENOM
------- -------- --------- ----------
1 M. DUPONT Alain
2 M. MARTIN Marc
3 M. BOUVIER Alain
4 M. DUBOIS Paul
5 M. DREYFUS Jean
6 M. FAURE Alain
7 M. LACOMBE Paul
8 Melle. DUHAMEL Evelyne
9 Mme. BOYER Martine
10 M. MARTIN Martin
...
L'exécution de la requête de l'exemple 9 donne :
Serveur: Msg 2627, Niveau 14, État 1, Ligne 1
Violation de la contrainte PRIMARY KEY 'PK_T_CLIENT'.
Impossible d'insérer une clé en double dans l'objet 'T_CLIENT'.
L'instruction a été arrêtée.
Et oui, car on ne peut insérer des clefs en double, or nous tentons d'insérer un deuxième client portant la clef 1, un autre portant le clef 2, etc... En l'occurence aucune ligne n'est donc insérée car toute requête de mise à jour est une transaction et fonctionne en "tout ou rien", c'est à direq qu'aucune ligne n'est insérée si au moins une contrainte n'est pas vérifié. En l'occurence la violation de la contrainte de clef primaire étant ici évidente rien n'est inséré dans la table T_CLIENT.
Nous savons à la lecture des données de la table des clients, que la clef la plus haute possède la valeur 100. Il est donc possible d'insérer des clients dont la clef est supérieure à cette valeur. D'ou l'idée de rajouter cette valeur à la valeur de clef de la table T_PROSPECT récupérée à l'aide de la requête SELECT, par une simple addition :
Mais comme nous sommes déjà en train de traiter une sous requête SELECT, il semble facile de remplacer la valeur 100 par une autre sous requête renvoyant la valeur maximale de la clef de la table T_CLIENT afin de l'ajouter à la valeur de la clef de la table T_PROSPECT :
Exemple 12
INSERTINTO T_CLIENT (CLI_ID,
TIT_CODE, CLI_NOM, CLI_PRENOM)
SELECT PRP_ID + (SELECTMAX(CLI_ID)
FROM T_CLIENT),
PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT
L'explication est simple : la sous requête (SELECT MAX...) n'est exécutée qu'une seule fois puisque qu'elle n'est pas corrélée avec la table cible de l'insertion.
1.6. Insertion multiple et conditionnelle à base de sous requête SELECT corrélée
Il est possible de corréler la sous requête d'insertion avec la requête cible. Dans ce cas il faut préciser une seconde fois la table cible dans la clause FROM de la sous requête SELECT. Pour reprendre notre exemple, nous voudrions insérer en tant que client les prospects dont le couple de valeurs nom/prenom est différent. Dans ce cas, les prospects 1 et 2 ne doivent pas être insérés, mais le prospect 3 (Dubois) doit, lui, être inséré.
Décomposons notre travail... Première phase, trouvons la requête qui va récupérer les client qui sont des prospects :
SELECT PRP_ID, PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT P
JOIN T_CLIENT C
ON C.CLI_NOM = P.PRP_NOM
Qui donne :
PRP_ID PRP_CODE_TITRE PRP_NOM PRP_PRENOM
----------- -------------- -------------------------------- ----------
1 M. Dupont Alain
3 M. Dubois NULL
NOTA : Si vous avez inséré les données des exemples précédents, veuillez procéder à la suppression des prospects insérés à l'aide de la requête DELETE FROM T_CLIENT WHERE CLI_ID > 100
Pour trouver l'inverse, c'est à dire les prospects qui ne sont pas des clients, il suffit de réaliser une exclusion. On peut alors utiliser l'ordre EXCEPT s'il est implémenté :
SELECT PRP_ID, PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT P
EXCEPT
SELECT CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM
FROM T_CLIENT
ou encore une construction équivalente, par exemple avec des jointures externes :
SELECT PRP_ID, PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT P
LEFTOUTERJOIN T_CLIENT C
ON C.CLI_NOM <> P.PRP_NOM
AND C.CLI_PRENOM <> P.PRP_PRENOM
WHERE CLI_ID ISNULL
Dès lors, l'insertion devient simple si l'on oublie pas de rajouter à la clef la valeur maximale de la clef de T_CLIENT...
Exemple 13
INSERTINTO T_CLIENT (CLI_ID,
TIT_CODE, CLI_NOM, CLI_PRENOM)
SELECT PRP_ID + (SELECTMAX(CLI_ID)
FROM T_CLIENT),
PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT P
LEFTOUTERJOIN T_CLIENT C
ON C.CLI_NOM <> P.PRP_NOM
AND C.CLI_PRENOM <> P.PRP_PRENOM
WHERE CLI_ID ISNULL
1.7. Insertion en auto référence
L'insertion en auto référence consiste à ajouter à une table une ou plusieurs nouvelles lignes calculées d'après les lignes existantes de la table cible.
Par exemple nous voulons insérer dans la tables des tarifs une nouvelle ligne avec comme date d'application du tarif, le premier janvier 2002, le même taux de taxe que le tarif précédent et un prix de petit déjeuner de 10% de plus que le précédent tarif.
Obtenir le précédent tarif consiste à trouver la ligne de la table dont la valeur de la date est maximale. Cela peut s'effectuer à l'aide de la requête suivante :
SELECT *
FROM T_TARIF
WHERE TRF_DATE_DEBUT = (SELECTMAX(TRF_DATE_DEBUT)
FROM T_TARIF)
Dès lors on peut utiliser cet ordre SELECT en le modifiant un peu de manière à lui faire insérer la nouvelle ligne tarifaire :
Exemple 14
INSERTINTO T_TARIF (TRF_DATE_DEBUT, TRF_TAUX_TAXES, TRF_PETIT_DEJEUNE)
SELECT '2002-01-01', TRF_TAUX_TAXES, TRF_PETIT_DEJEUNE * 1.1
FROM T_TARIF
WHERE TRF_DATE_DEBUT = (SELECTMAX(TRF_DATE_DEBUT)
FROM T_TARIF)
REMARQUE : Voici les principaux cas pour lesquels un ordre d'insertion ne peut aboutir :
violation de clef (index primaire)
violation de contrainte d'index secondaire unique
violation de contrainte de données (colonne not null)
violation d'intégrité référentielle
violation de contrainte de contrôle de validité (min, max, étendue, domaine...)
2. Suppression à l'aide de DELETE
Vous trouverez des compléments d'information sur le sujet aux pages 243 à 244 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.
La syntaxe de base de l'ordre SQL de suppression de données dans une table est la suivante :
DELETE [FROM] nom_table_cible
[WHERE condition]
Le seul cas pour lequel cet ordre peut ne pas aboutir est lorsque la suppression viole la contrainte d'intégrité référentielle. Il est en effet absurde de vouloir supprimer un client si les factures relatives à ce client n'ont pas été préalablement supprimées.
NOTA : Dans certains cas, il se peut que la suppression d'une ligne entraîne la suppressions d'autres lignes dans d'autres tables lorsqu'existe des intégritées référentielles de suppression en cascade.
2.1. Suppression de toutes les lignes d'une table
C'est la forme la plus simple de l'ordre DELETE puisqu'il suffit d'omettre la clause WHERE :
Exemple 15
DELETEFROM T_PROSPECT
Supprime tous les prospects.
2.2. Suppression conditionnelle
Il suffit de rajouter la clause WHERE dotée d'un prédicat.
Exemple 16
DELETEFROM T_PROSPECT
WHERE PRP_PRENOM LIK'%d'
Supprime tous les prospects dont le nom se termine par la lettre 'd'.
2.3. Suppression avec sous requête conditionnelle
Il est possible d'utiliser une sous requête conditionnelle dans la clause WHERE d'un ordre DELETE.
Supprimons les prospects dont le couple de valeurs nom/prénom se trouve dans la table des clients. Procédons pour cela par étape. Pour obtenir la liste des prospects qui figurent en tant que client, nous pouvons faire la requête suivante :
SELECT PRP_ID, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT P
JOIN T_CLIENT C
ON C.CLI_NOM = P.PRP_NOM
AND C.CLI_PRENOM = P.PRP_PRENOM
Dès lors il suffit de supprimer les prospects dont l'identifiant est récupéré par la sous requête :
Exemple 17
DELETEFROM T_PROSPECT
WHERE PRP_ID = (SELECT PRP_ID
FROM T_PROSPECT P
JOIN T_CLIENT C
ON C.CLI_NOM = P.PRP_NOM
AND C.CLI_PRENOM = P.PRP_PRENOM)
On peut procéder aussi à l'aide du constructeur de lignes valuées, si votre SGBDR le supporte, ce qui simplifie l'écriture de la requête :
Exemple 18
DELETEFROM T_PROSPECT
WHERE (CLI_NOM, CLI_PRENOM) = (SELECT PRP_NOM, PRP_PRENOM
FROM T_CLIENT)
Bien entendu la construction de sous requêtes dans un ordre DELETE peut être compliqué à souhait afin d'effectuer la suppression conditionnelle désirée...
3. Modification à l'aide d'UPDATE
Vous trouverez des compléments d'information sur le sujet aux pages 239 à 242 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.
La syntaxe de base de l'ordre SQL de modification de données dans une table est la suivante :
3.1. Mise à jour d'une colonne unique sans condition
C'est la forme la plus simple de l'ordre UPDATE. Nous voulons par exemple fixer à 55 F les tarifs de nos petits déjeuners dans la table T_TARIF :
Exemple 19
UPDATE T_TARIF
SET TRF_PETIT_DEJEUNE = 55
3.2. Mise à jour d'une colonne unique avec reprise de valeur (auto référence)
On peut aussi reprendre la valeur de la colonne (ou d'une autre colonne de la table cible). Par exemple nous pouvons demander une augmentation de 15% des tarifs des petits déjeuners :
Exemple 20
UPDATE T_TARIF
SET TRF_PETIT_DEJEUNE = TRF_PETIT_DEJEUNE * 1.15
3.3. Mise à jour d'une colonne unique avec filtrage
On peut ajouter une clause de filtrage WHERE dans une requête de mise à jour. Par exemple nous pouvons décider de n'augementer de 15% que les tarifs des petits déjeuners des périodes postérieures à 1999.
Exemple 21
UPDATE T_TARIF
SET TRF_PETIT_DEJEUNE = TRF_PETIT_DEJEUNE * 1.15
WHERE EXTRACT(YEAR FROM TRF_DATE_DEBUT) > 1999
3.4. Mise à jour de plusieurs colonnes simultanément
Pour mettre à jour simultanément plusieurs colonnes, il suffit de répéter aitant de fois que nécessaire le contenu de la clause SET, à raison d'un couple colonne/valeur par colonne visées par la mise à jour.
NOTA : dans ce cas, la nullité de l'exécution de modification d'une valeur dans une colonne possédant le marqueur NULL, n'entraîne pas la nullité de l'exécution des mises à jour des autres colonnes, chaque modification de colonne étant évaluées séparément.
3.5. Mise à jour avec sous requête
Comme dans les ordres INSERT et DELETE, il est possible d'utiliser une sous requête dans la clause WHERE de l'ordre UPDATE afin de filtrer de manière plus complète.
Par exemple, afin d'éviter de confondre des prospects qui ont le même nom et prénom que certains clients, on désire ajouter le mot "bis" aux prospects homonymes :
Exemple 23
UPDATE T_PROSPECT
SET PRP_NOM = TRIM(RIGHT, PRP_NOM) || ' bis'
WHERE PRP_ID = (SELECT PRP_ID
FROM T_PROSPECT P
JOIN T_CLIENT C
ON C.CLI_NOM = P.PRP_NOM
AND C.CLI_PRENOM = P.PRP_PRENOM)
3.6. Mise à jour de valeurs particulières (défaut et marqueur NULL)
Il est possible de mettre à jour une colonne à sa valeur par défaut si elle possède une telle spécificité élaborée dans la création de la table :
En reprenant la définition des tables de connexion vu au paragraphe 1.3, donnons à la colonne CNX_USER sa valeur par défaut pour toutes les lignes de la table :
Exemple 24
UPDATE T_SUIVI_CONNEXION
SET CNX_USER = DEFAULT
Il est aussi possible de supprimer le contenu d'une colonne (ou de plusieurs) en y plaçant le marqueur NULL :
Exemple 25
UPDATE T_CLIENT
SET CLI_ENSEIGNE = NULL
Cette requête vide la colonne CLI_ENSEIGNE de la table des clients en y plaçant NULL.
NOTA : c'est le seul cas ou l'on trouvera le mot clef NULL associé au signe égal, car dans ce cas le signe egal est un opérateur d'affectation. De manière syntaxique il aurait mieux valut une construction du genre :
UPDATE T_CLIENT
SET CLI_ENSEIGNE ASNULL
Mais ceci est une autre histoire !
ATTENTION : Une mise à jour peut échouer si elle viole les contraintes. Voici les principaux cas pour lesquels un ordre de modification ne peut aboutir :
violation de clef (index primaire)
violation de contrainte d'index secondaire unique
violation de contrainte de données (colonne not null)
violation d'intégrité référentielle
violation de contrainte de contrôle de validité (min, max, étendue, domaine...)
4. Valeurs ambigües
Il arrive lors des insertions et des mise à jour que la valeur passée en argument soit ambigüe car son format ou son type ne peut être exprimé que par l'intermédiaire du jeu de caractère ordinaire. Comment donc savoir si la chaîne "AF12" est une chaîne de caractères ou un code hexadécimal représentant 4 octets soit 2 caractères ?
Pour lever cette ambigüité, on doit utiliser une lettre de préfixage :
