SQL
7. Les mises à jour en SQL - INSERT, DELETE, UPDATE
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.
1. Préambule
2. Insérer à l'aide d'INSERT
2-A. Insertion simple explicite
2-B. Insertion multiple explicite à l'aide du constructeur de lignes valuées
2-C. Insertion partiellement explicite avec le mot clef DEFAULT
2-D. Insertion totalement implicite avec l'expression DEFAULT VALUES
2-E. Insertion multiple à base de sous requête SELECT
2-F. Insertion multiple et conditionnelle à base de sous requête SELECT corrélée
2-G. Insertion en auto-référence
3. Suppression à l'aide de DELETE
3-A. Suppression de toutes les lignes d'une table
3-B. Suppression conditionnelle
3-C. Suppression avec sous requête conditionnelle
4. Modification à l'aide d'UPDATE
4-A. Mise à jour d'une colonne unique sans condition
4-B. Mise à jour d'une colonne unique avec reprise de valeur (auto référence)
4-C. Mise à jour d'une colonne unique avec filtrage
4-D. Mise à jour de plusieurs colonnes simultanément
4-E. Mise à jour avec sous-requête
4-F. Mise à jour de valeurs particulières (défaut et marqueur NULL)
5. Valeurs ambigües
6. Résumé
1. Préambule
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 alors
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.
2. Insérer à l'aide d'INSERT
La syntaxe de base de l'ordre SQL d'insertion de données dans une table est la suivante :
INSERT [ INTO ] nom_de_la_table_cible [ (liste_des_colonnes_visées) ]
{VALUES (liste_des_valeurs) | requête_select | DEFAULT VALUES }
|
NOTA
-
la liste des colonnes visées peut être omise à 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).
![warning](../SQL/kitwarning.gif) |
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.
|
![info](../SQL/kitinfo.gif) |
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 défaut.
Nous allons maintenant détailler ces différentes déclinaisons de l'ordre INSERT.
2-A. 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.
Exemple 1 :
INSERT INTO T_MODE_PAIEMENT (PMT_CODE, PMT_LIBELLE)
VALUES (' CB ' , ' Carte bancaire ' )
|
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 :
Exemple 2 :
INSERT INTO T_MODE_PAIEMENT
VALUES (' CB ' , ' Carte bancaire ' )
|
Qui, bien évidemment, donne le même résultat.
![info](../SQL/kitinfo.gif) |
Lorsqu'une valeur n'est pas connue, il est possible de préciser le mot clef NULL (marqueur)
qui laisse la colonne vide.
|
Exemple 3 :
INSERT INTO T_MODE_PAIEMENT
VALUES (' X ' , NULL )
|
Qui insère un mode de paiement de code "X" et dont le libellé n'est pas renseigné.
2-B. Insertion multiple explicite à l'aide du constructeur de lignes valuées
Cette syntaxe porte sur l'insertion de multiples ligne au sein de la table cible. Il faut préciser les
lignes de valeurs à insérer explicitement.
Exemple 4 :
INSERT T_TITRE (TIT_CODE, TIT_LIBELLE)
VALUES (' M. ' , ' Monsieur ' ,
' Mlle. ' , ' Mademoiselle '
' Mme. ' , ' Madame ' )
|
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 :
Exemple 5 :
INSERT T_TITRE
VALUES (' M. ' , ' Monsieur ' ,
' Mlle. ' , ' Mademoiselle '
' Mme. ' , ' Madame ' )
|
et donne le même résultat que l'exemple 4.
Le constructeur de lignes valuées consiste à donner une liste de lignes en argument.
![info](../SQL/kitinfo.gif) |
Le constructeur de lignes valuées est rarement implémenté dans les SGBDR. Oracle est l'un
de seuls à accepter une telle syntaxe.
|
2-C. 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-clé 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 :
CREATE TABLE T_SUIVI_CONNEXION
(CNX_USER VARCHAR (128 ) NOT NULL DEFAULT ' Administrateur ' ,
CNX_DATE_HEURE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)
|
Alors nous pouvons insérer l'heure et la date par défaut sans en préciser la valeur, à l'aide de la requête :
Exemple 6 :
INSERT INTO T_SUIVI_CONNEXION (CNX_USER, CNX_DATE_HEURE)
VALUES (' Dupont ' , DEFAULT )
|
Qui insère automatiquement la valeur par défaut dans la colonne CNX_DATE_HEURE.
Bien entendu on peut omettre la liste des noms de colonnes puisque, à nouveau, toutes les colonnes sont
concernées par l'ordre d'insertion :
Exemple 7 :
INSERT INTO T_SUIVI_CONNEXION
VALUES (' Dupont ' , DEFAULT )
|
Qui donne le même résultat si l'ordre est exécuté au même moment !
On peut aussi ne donner qu'une liste partielle des colonnes visées, les autres dotées d'une valeur par
défaut seront automatiquement alimentées :
Exemple 8 :
INSERT INTO T_SUIVI_CONNEXION (CNX_USER)
VALUES (' Dupont ' )
|
Qui donne encore le même résultat si l'ordre est exécuté au même moment !
2-D. 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-clé DEFAULT VALUES. Dans
ce cas il ne faut pas préciser les noms des colonnes :
Exemple 9 :
INSERT INTO T_SUIVI_CONNEXION
DEFAULT VALUES
|
Qui insérera l'utilisateur "Administrateur" avec la date et l'heure courante.
2-E. 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 identique 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 dans notre base de données exemple, la table
des prospects et d'y insérer explicitement quelques données :
CREATE TABLE T_PROSPECT
(PRP_ID INTEGER NOT NULL ,
PRP_CODE_TITRE CHAR (4 ) NULL ,
PRP_NOM CHAR (25 ) NOT NULL ,
PRP_PRENOM VARCHAR (16 ) NULL ,
PRP_ENSEIGNE VARCHAR (60 ) NULL ,
PRP_DATE_SAISIE TIMESTAMP NOT NULL DEFAULT CURRENT_DATETIME,
CONSTRAINT PK_T_PROSPECT PRIMARY KEY (PRP_ID))
|
|
INSERT INTO T_PROSPECT (PRP_ID, PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM, PRP_ENSEIGNE, PRP_DATE_SAISIE)
VALUES (1 , ' M. ' , ' Dupont ' ,' Alain ' , NULL , DEFAULT )
INSERT INTO T_PROSPECT
VALUES (2 , ' Mme. ' , ' Durand ' ,' Aline ' , ' SNCF ' , ' 2000-12-25 ' )
INSERT INTO T_PROSPECT (PRP_ID, PRP_CODE_TITRE, PRP_NOM )
VALUES (3 , ' M. ' , ' Dubois ' )
|
|
La table T_PROSPECT contient donc :
PRP_ID PRP_CODE_TITRE PRP_NOM PRP_PRENOM PRP_ENSEIGNE PRP_DATE_SAISIE
------- -------------- ---------- ------------ -------------- -----------------------
1 M. Dupont Alain NULL 2002-03-16 00:00:00.000
2 Mme. Durand Aline SNCF 2000-12-25 00:00:00.000
3 M. Dubois NULL NULL 2002-03-16 00:00:00.000
|
Un premier exemple, que nous allons décortiquer, va nous permettre de comprendre comment fonctionne l'insertion
avec une sous-requête SELECT :
Exemple 10 :
INSERT INTO T_CLIENT (CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM)
SELECT PRP_ID, PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT
|
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-à-dire qu'aucune ligne n'est insérée si au moins une contrainte
n'est pas vérifiée. 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'où
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 :
Et là, nos trois prospects ont été insérés :
CLI_ID TIT_CODE CLI_NOM CLI_PRENOM CLI_ENSEIGNE
------- -------- -------- ----------- ------------
101 M. Dupont Alain NULL
102 Mme. Durand Aline NULL
103 M. Dubois NULL NULL
|
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 :
INSERT INTO T_CLIENT (CLI_ID,
TIT_CODE, CLI_NOM, CLI_PRENOM)
SELECT PRP_ID + (SELECT MAX (CLI_ID)
FROM T_CLIENT),
PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT
|
Ce qui s'exécute parfaitement et donne :
CLI_ID TIT_CODE CLI_NOM CLI_PRENOM CLI_ENSEIGNE
------- -------- -------- ----------- ------------
104 M. Dupont Alain NULL
105 Mme. Durand Aline NULL
106 M. Dubois NULL NULL
|
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.
2-F. 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/prénom 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 clients 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
LEFT OUTER JOIN T_CLIENT C
ON C.CLI_NOM < > P.PRP_NOM
AND C.CLI_PRENOM < > P.PRP_PRENOM
WHERE CLI_ID IS NULL
|
Qui, toutes deux donnent :
PRP_ID PRP_CODE_TITRE PRP_NOM PRP_PRENOM
----------- -------------- -------------------------------- ----------
3 M. Dubois NULL
|
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 :
INSERT INTO T_CLIENT (CLI_ID,
TIT_CODE, CLI_NOM, CLI_PRENOM)
SELECT PRP_ID + (SELECT MAX (CLI_ID)
FROM T_CLIENT),
PRP_CODE_TITRE, PRP_NOM, PRP_PRENOM
FROM T_PROSPECT P
LEFT OUTER JOIN T_CLIENT C
ON C.CLI_NOM < > P.PRP_NOM
AND C.CLI_PRENOM < > P.PRP_PRENOM
WHERE CLI_ID IS NULL
|
2-G. 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 = (SELECT MAX (TRF_DATE_DEBUT)
FROM T_TARIF)
|
qui donne
TRF_DATE_DEBUT TRF_TAUX_TAXES TRF_PETIT_DEJEUNE
-------------- -------------- -----------------
2002-01-01 20.6000 69.5750
|
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 :
INSERT INTO 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 = (SELECT MAX (TRF_DATE_DEBUT)
FROM T_TARIF)
|
![info](../SQL/kitinfo.gif) |
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, etc.).
3. Suppression à l'aide de DELETE
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.
![info](../SQL/kitinfo.gif) |
Dans certains cas, il se peut que la suppression d'une ligne entraîne la suppressions d'autres
lignes dans d'autres tables lorsqu'il existe des intégritées référentielles de suppression en cascade.
|
3-A. 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 :
Cette requête supprime tous les prospects.
3-B. Suppression conditionnelle
Il suffit de rajouter la clause WHERE dotée d'un prédicat.
Exemple 16 :
DELETE FROM T_PROSPECT
WHERE PRP_PRENOM LIK' %d '
|
Cette requête supprime tous les prospects dont le nom se termine par la lettre 'd'.
3-C. 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
|
|
PRP_ID PRP_NOM PRP_PRENOM
----------- -------------- ----------
1 Dupont Alain
|
|
Dès lors il suffit de supprimer les prospects dont l'identifiant est récupéré par la sous requête :
Exemple 17 :
DELETE FROM 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 :
DELETE FROM 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.
4. Modification à l'aide d'UPDATE
La syntaxe de base de l'ordre SQL de modification de données dans une table est la suivante :
UPDATE nom_table_cible
SET colonne = valeur [ , colonne2 = valeur2 ... ]
[ WHERE condition ]
|
4-A. Mise à jour d'une colonne unique sans condition
C'est la forme la plus simple de l'ordre UPDATE. Nous voulons par exemple fixer à 8€ les tarifs de
nos petits déjeuners dans la table T_TARIF :
Exemple 19 :
UPDATE T_TARIF
SET TRF_PETIT_DEJEUNE = 8
|
4-B. 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
|
4-C. 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
|
4-D. Mise à jour de plusieurs colonnes simultanément
Pour mettre à jour simultanément plusieurs colonnes, il suffit de répéter autant 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.
Exemple 22 :
UPDATE T_CLIENT
SET CLI_NOM = UPPER (CLI_NOM),
CLI_PRENOM = UPPER (CLI_PRENOM)
CLI_ENSEIGNE = UPPER (CLI_ENSEIGNE)
|
![info](../SQL/kitinfo.gif) |
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.
|
4-E. 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)
|
4-F. 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-clé 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 AS NULL
|
Mais ceci est une autre histoire !
![warning](../SQL/kitwarning.gif) |
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, etc.).
5. 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 :
N
|
Unicode (National)
|
B
|
Bit
|
X
|
Hexadécimal
|
Exemple 26 :
CREATE TABLE T_NOMECLATURE
(NMC_REFERENCE NATIONAL CHAR (13 ) NOT NULL PRIMARY KEY ,
NMC_COMPOSANT BIT (1 ) NOT NULL DEFAULT 0 ,
NMC_ADRESSE_MEMOIRE VARBINARY (4 ))
INSERT INTO T_NOMECLATURE (NMC_REFERENCE, NMC_COMPOSANT, NMC_ADRESSE_MEMOIRE)
VALUES (N' 747 XWC-3344 ' , B' 0 ' , X' AF12 ' )
|
6. Résumé
Voici les différences entre les moteurs des bases de données :
Mise à jour des données
|
Paradox
|
Access
|
Sybase
|
SQL Server
|
Oracle
|
MySQL
|
Interbase
|
PostGreSQL
|
INSERT explicite
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
INSERT explicite multi-lignes
|
Non
|
Non
|
Non
|
Non
|
Oui
|
Non
|
Non
|
Non
|
INSERT avec DEFAULT
|
Oui (1)
|
|
|
Oui
|
|
|
|
|
INSERT avec DEFAULT VALUES
|
Non
|
|
|
Oui
|
|
|
|
|
INSERT avec sous-requête
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Non
|
Oui
|
Oui
|
INSERT avec sous-requête corrélée
|
Oui
|
|
Oui
|
Oui
|
Oui
|
Non
|
Oui
|
Oui
|
INSERT en auto-référence
|
Oui
|
|
|
Oui
|
|
|
|
|
DELETE (vidage de table)
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
DELETE avec filtre WHERE
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
DELETE avec sous-requête
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Non
|
Oui
|
Oui
|
UPDATE colonne unique
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
UPDATE en auto-référence
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
UPDATE multi-colonnes
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
UPDATE avec sous-requête
|
Oui
|
Oui
|
Oui
|
Oui
|
Oui
|
Non
|
Oui
|
Oui
|
UPDATE avec NULL / DEFAULT
|
Oui/Non
|
|
|
Oui
|
|
|
|
|
Lettre de préfixage
|
Non
|
|
|
Oui (2)
|
|
|
|
|
(1) en omettant de préciser la colonne
(2) pas toutes