Objectif

Utilisez l’instruction UPDATE pour modifier les valeurs existantes dans une table ou dans la table de base d’une vue ou la table principale d’une vue matérialisée.

Rubriques supplémentaires

• Prérequis

• Syntaxe

• Semantics

• Exemples

Prérequis

Pour pour mettre à jour des valeurs dans une table, la table doit être dans votre propre schéma ou vous devez avoir le privilège d’objet UPDATE sur la table.

Pour que vous puissiez mettre à jour valeurs dans la table de base d’une vue:

• Vous devez avoir le privilège d’objet UPDATE sur la vue, et

• Le propriétaire du schéma contenant la vue doit disposer du privilège d’objet UPDATE sur la table de base.

Le privilège système UPDATE ANY TABLE vous permet également de mettre à jour les valeurs de tout table ou dans la table de base de n’importe quelle vue.

Vous devez également disposer du privilège d’objet SELECT sur l’objet que vous souhaitez mettre à jour si:

• L’objet se trouve sur une base de données distante ou

• Le paramètre d’initialisation SQL92_SECURITY est défini sur TRUE et l’opération UPDATE fait référence à des colonnes de table, telles que les colonnes d’un where_clause.

Syntaxe

update :: =

Description de l’illustration update.gif

(DML_table_expression_clause :: =, update_set_clause :: =, where_clause :: =, return_clause :: =, error_logging_clause :: =)

DML_table_expression_clause :: =

Description de l’illustration DML_table_expression_clause.gif

(subquery :: = – partie de SELECT, subquery_restriction_clause :: =, table_collection_expression :: =)

subquery_restriction_clause :: =

Description de l’illustration subquery_restriction_clause.gif

table_collection_expression :: =

Description de l’illustration table_collection_expression.gif

update_set_clause :: =

Description de l’illustration update_set_clause. gif

where_clause :: =

Description de l’illustration where_clause.gif

return_clause :: =

Description de l’illustration retournant_clause.gif

error_logging_clause :: =

Description de l’illustration error_logging_clause.gif

Sémantique

indice

Spécifiez un commentaire qui transmet des instructions à l’optimiseur sur le choix d’un plan d’exécution pour l’instruction.

Vous pouvez placer un indice parallèle immédiatement après le mot-clé UPDATE pour paralléliser les deux des opérations de scan et de UPDATE.

DML_table_expression_clause

La clause ONLY s’applique uniquement aux vues. Spécifiez la syntaxe ONLY si la vue de la clause UPDATE est une vue appartenant à une hiérarchie et que vous ne souhaitez pas mettre à jour les lignes de de ses sous-vues.

schéma

Spécifiez le schéma contenant l’objet à mettre à jour. Si vous omettez schema, la base de données suppose que l’objet se trouve dans votre propre schéma.

table | vue | materialized_view | subquery

Spécifiez le nom de la table, de la vue, de la vue matérialisée ou des colonnes renvoyées par une sous-requête à mettre à jour. L’émission d’une instruction UPDATE sur une table déclenche tous les déclencheurs UPDATE associés à la table.

• Si vous spécifiez view, la base de données met à jour la table de base de la vue.Vous ne pouvez pas mettre à jour une vue sauf avec les déclencheurs INSTEAD OF si la requête de définition de la vue contient l’une des constructions suivantes:

Un opérateur d’ensemble Un opérateurDISTINCTUne fonction d’agrégation ou d’analyse AGROUPBY,ORDERBY,MODEL,CONNECTBYouSTARTWITHclause Une expression de collection dans unSELECTliste Une sous-requête dans uneSELECTliste Une sous-requête désignéeWITH READ ONLYJoint, à quelques exceptions près , comme indiqué dans le manuel Oracle Database Administrator « s Guide

• Vous ne pouvez pas mettre à jour plusieurs tables de base via une vue.

• De plus, si le la vue a été créée avec WITH CHECK , vous ne pouvez mettre à jour la vue que si les données résultantes satisfont à la requête de définition de la vue.

• Si table ou la table de base de view contient une ou plusieurs colonnes d’index de domaine, puis cette instruction exécute la routine de mise à jour d’indextype appropriée.

• Vous ne pouvez pas mettre à jour les lignes dans une vue matérialisée en lecture seule. Si vous mettez à jour des lignes dans une vue matérialisée accessible en écriture, la base de données met à jour les lignes de la table conteneur sous-jacente. Cependant, les mises à jour sont écrasées lors de la prochaine opération d’actualisation. Si vous mettez à jour des lignes dans une vue matérialisée pouvant être mise à jour faisant partie d’un groupe de vues matérialisées, la base de données met également à jour les lignes correspondantes dans la table principale.

PARTITION | SUBPARTITION

Spécifiez le nom de la partition ou de la sous-partition dans table ciblée pour les mises à jour. Vous n’avez pas besoin de spécifier le nom de la partition lors de la mise à jour des valeurs dans une table partitionnée. Cependant, dans certains cas, la spécification du nom de la partition peut être plus efficace qu’un where_clause compliqué .

dblink

Spécifier un nom complet ou partiel d’un lien de base de données vers une base de données distante où t L’objet est localisé. Vous ne pouvez utiliser un lien de base de données pour mettre à jour un objet distant que si vous utilisez la fonctionnalité distribuée d’Oracle Database.

Si vous omettez dblink,, la base de données suppose que l’objet est sur la base de données locale.

subquery_restriction_clause

Utilisez subquery_restriction_clause pour restreindre la sous-requête de l’une des manières suivantes:

AVEC LECTURE UNIQUEMENT Spécifiez WITH READ ONLY pour indiquer que la table ou la vue ne peut pas être mise à jour.

AVEC CHECK OPTION Spécifiez WITH CHECK OPTION pour indiquer qu’Oracle Database interdit toute modification de la table ou de la vue qui produirait lignes qui ne sont pas incluses dans la sous-requête. Lorsqu’elle est utilisée dans la sous-requête d’une instruction DML, vous pouvez spécifier cette clause dans une sous-requête de la clause FROM mais pas dans la sous-requête de la WHERE clause.

Contrainte CONSTRAINT Spécifiez le nom de la contrainte CHECK OPTION. Si vous omettez cet identifiant, Oracle attribue automatiquement à la contrainte un nom de la forme SYS_C n, où n est un entier qui rend le nom de contrainte unique dans la base de données.

expression_collection_table

Le table_collection_expression vous permet d’informer Oracle que la valeur de collection_expression doit être traitée comme une table pour les opérations de requête et DML. Le collection_expression peut être une sous-requête, une colonne, une fonction ou un constructeur de collection. Quelle que soit sa forme, il doit renvoyer une valeur de collection, c’est-à-dire une valeur dont le type est table imbriquée ou varray. Ce processus d’extraction des éléments d’une collection s’appelle la suppression de l’imbrication de la collection.

Le signe plus (+) facultatif est pertinent si vous joignez l’expression TABLE avec le parent table. Le + crée une jointure externe des deux, de sorte que la requête renvoie des lignes de la table externe même si l’expression de collection est nulle.

Vous pouvez utiliser un table_collection_expression pour mettre à jour les lignes d’une table en fonction des lignes d’une autre table. Par exemple, vous pouvez regrouper quatre tables de ventes trimestrielles dans une table de ventes annuelle.

t_alias

Spécifiez un nom de corrélation (alias) pour la table, la vue ou la sous-requête à référencer ailleurs dans la déclaration. Cet alias est obligatoire si DML_table_expression_clause fait référence à des attributs de type d’objet ou à des méthodes de type d’objet.

Restrictions sur la clause DML_table_expression_clause Cette clause est soumise aux restrictions suivantes:

• Vous ne pouvez pas exécuter cette instruction si table ou la table de base de view contient tous les index de domaine marqués IN_PROGRESS ou FAILED.

• Vous ne pouvez pas insérer dans une partition si des partitions d’index affectées sont marquées UNUSABLE.

• Vous ne pouvez pas spécifier le order_by_clause dans la sous-requête de DML_table_expression_clause.

• Si vous spécifiez un index, une partition d’index ou une sous-partition d’index qui a été marqué UNUSABLE, l’instruction UPDATE échouera à moins que SKIP_UNUSABLE_INDEXES paramètre de session a été défini sur TRUE.

update_set_clause

Le update_set_clause vous permet de définir des valeurs de colonne.

colonne

Spécifiez le nom d’une colonne de l’objet à mettre à jour. Si vous omettez une colonne du tableau dans update_set_clause, la valeur de cette colonne reste inchangée.

Si column fait référence à un attribut d’objet LOB, vous devez d’abord l’initialiser avec une valeur vide ou null. Vous ne pouvez pas le mettre à jour avec un littéral. De plus, si vous mettez à jour une valeur LOB à l’aide d’une méthode autre qu’une instruction SQL UPDATE directe, vous devez d’abord verrouiller la ligne contenant le LOB. Voir for_update_clause pour plus d’informations.

Si column fait partie de la clé de partitionnement d’une table partitionnée, alors UPDATE échouera si vous modifiez une valeur dans la colonne qui déplacerait la ligne vers une autre partition ou sous-partition, sauf si vous activez le mouvement de ligne. Veuillez vous référer au row_movement_clause de CREATE TABLE ou ALTER TABLE.

De plus, si column fait partie du clé de partitionnement d’une table partitionnée par liste, alors UPDATE échouera si vous spécifiez une valeur pour la colonne qui n’existe pas déjà dans partition_value liste de l’une des partitions.

sous-requête

Spécifiez une sous-requête qui renvoie exactement une ligne pour chaque ligne mise à jour.

• Si vous ne spécifiez qu’une seule colonne dans update_set_clause, alors la sous-requête ne peut renvoyer qu’une seule valeur.

• Si vous spécifiez plusieurs colonnes dans le update_set_clause, alors la sous-requête doit renvoyer autant de valeurs que vous avez spécifié de colonnes.

• Si la sous-requête ne renvoie aucune ligne, alors le La colonne reçoit une valeur nulle.

• Si ce subquery fait référence à des objets distants, alors le UPDATE peut s’exécuter en parallèle tant que le referen ce ne retourne pas à un objet de la base de données locale. Cependant, si subquery dans le DML_table_expression_clause fait référence à des objets distants, alors UPDATE l’opération s’exécutera en série sans notification.

Vous pouvez utiliser flashback_query_clause dans la sous-requête pour mettre à jour table avec les données passées. Veuillez vous référer à la flashback_query_clause de SELECT pour plus d’informations sur cette clause.

expr

Spécifiez une expression qui résout la nouvelle valeur attribuée à la colonne correspondante.

DEFAULT Spécifiez DEFAULT pour définir la colonne sur la valeur précédemment spécifiée comme valeur par défaut valeur de la colonne. Si aucune valeur par défaut pour la colonne correspondante n’a été spécifiée, la base de données définit la colonne sur null.

Restriction sur la mise à jour des valeurs par défaut Vous ne pouvez pas spécifier DEFAULT si vous mettez à jour une vue.

Clause VALUE

La clause VALUE vous permet de spécifier la ligne entière d’une table d’objets.

Restriction sur la clause VALUE Vous ne pouvez spécifier cette clause que pour une table d’objets.

where_clause

Le where_clause vous permet de limiter les lignes mises à jour à celles pour lesquelles le condition est vrai. Si vous omettez cette clause, la base de données met à jour toutes les lignes de la table ou de la vue. Veuillez vous référer au chapitre 7, « Conditions » pour la syntaxe de condition.

Le where_clause détermine les lignes dans quelles valeurs sont mises à jour. Si vous ne spécifiez pas where_clause, toutes les lignes sont mises à jour. Pour chaque ligne qui satisfait le where_clause, les colonnes à gauche de l’opérateur d’égalité (=) dans update_set_clause sont définies sur les valeurs des expressions correspondantes à droite de l’opérateur. Les expressions sont évaluées au fur et à mesure que la ligne est mise à jour.

return_clause

La clause de retour récupère les lignes affectées par une instruction DML. Vous pouvez spécifier cette clause pour les tables et les vues matérialisées et pour les vues avec une seule table de base.

Lorsque vous travaillez sur une seule ligne, une instruction DML avec un returning_clause peut récupérer des expressions de colonne en utilisant la ligne affectée, l’ID de ligne et REFs dans la ligne concernée et les stocker dans des variables hôtes ou des variables PL / SQL.

Lors de l’utilisation plusieurs lignes, une instruction DML avec returning_clause stocke les valeurs des expressions, des rowids et REFs impliquant les lignes affectées dans les tableaux de liaison.

expr Chaque élément de la liste expr doit être une syntaxe d’expression valide.

INTO Le INTO indique que les valeurs des lignes modifiées doivent être stockées dans la ou les variables spécifiées dans data_item list.

data_item Chaque data_item est une variable hôte ou une variable PL / SQL qui stocke la valeur expr récupérée.

Pour chaque expression de la liste RETURNING, vous devez spécifier une variable PL / SQL compatible avec le type correspondant ou une variable hôte dans le INTO list.

Restrictions Les restrictions suivantes s’appliquent à la clause RETURNING:

• Le expr est limité comme suit:

  • Pour UPDATE et DELETE instructions que chaque expr doit être une expression simple ou une expression de fonction d’agrégation à un seul ensemble. Vous ne pouvez pas combiner des expressions simples et des expressions de fonction d’agrégation à jeu unique dans le même returning_clause. Pour les instructions INSERT, chaque expr doit être une expression simple. Les fonctions d’agrégation ne sont pas prises en charge dans une clause INSERT instruction RETURNING.

  • Unique- Les expressions de fonction d’agrégation définies ne peuvent pas inclure le mot clé DISTINCT.

• Si le contient une colonne de clé primaire ou une autre colonne NOT NULL, puis l’instruction de mise à jour échoue si la table a un BEFORE UPDATE déclencheur défini dessus.

• Vous ne pouvez pas spécifier le returning_clause pour une insertion multitable.

• Vous ne pouvez pas utiliser cette clause avec un DML parallèle ou avec des objets distants.

• Vous ne pouvez pas récupérer les types LONG avec cette clause.

• Vous ne pouvez pas spécifier cette clause pour une vue sur laquelle un déclencheur INSTEAD OF a été défini.

error_logging_clause

La clause error_logging_clause a le même comportement dans une instruction UPDATE que dans une instruction INSERT. Veuillez vous référer à l’instruction INSERT error_logging_clause pour plus d’informations.

Exemples

Mise à jour d’une table: exemples Ce qui suit La déclaration donne des commissions nulles à tous les employés occupant le poste SH_CLERK:

UPDATE employees SET commission_pct = NULL WHERE job_id = "SH_CLERK";

La déclaration suivante fait la promotion de Douglas Grant au poste de directeur du département 20 avec une augmentation de 1 000 $:

UPDATE employees SET job_id = "SA_MAN", salary = salary + 1000, department_id = 120 WHERE first_name||" "||last_name = "Douglas Grant"; 

La déclaration suivante augmente le salaire d’un employé du employees table sur la base de données remote:

UPDATE employees@remote SET salary = salary*1.1 WHERE last_name = "Baer";

L’exemple suivant montre les constructions syntaxiques suivantes du UPDATE instruction:

• Les deux formes de update_set_clause ensemble dans une seule instruction

• Une sous-requête corrélée

• Un where_clause pour limiter les lignes mises à jour

L’instruction UPDATE précédente effectue les opérations suivantes:

• Met à jour uniquement les employés qui travaillent à Genève ou à Munich (emplacements 2900 et 2700)

• Définit department_id pour ces employés au department_id correspondant à Bombay (location_id 2100)

• Règle le salaire de chaque employé à 1,1 fois le salaire moyen de son service

• Règle la commission de chaque employé à 1,5 fois la commission moyenne de son service

Mise à jour d’une partition: exemple L’exemple suivant met à jour les valeurs dans une seule partition de la table sales:

UPDATE sales PARTITION (sales_q1_1999) s SET s.promo_id = 494 WHERE amount_sold > 1000;

Mise à jour d’une table d’objets: exemple L’instruction suivante crée deux tables d’objets, people_demo1 et people_demo2 , de l’objet people_typ créé dans les collections de tables: exemples. L’exemple montre comment mettre à jour une ligne de people_demo1 en sélectionnant une ligne dans people_demo2:

L’exemple utilise le VALUE fonction de référence d’objet dans la clause SET et dans la sous-requête.

Mise à jour corrélée: exemple Pour un exemple qui utilise une sous-requête corrélée pour mettre à jour les lignes de table imbriquées, veuillez vous reporter à « Collections de tables: exemples ».

Utilisation de la clause RETURNING pendant la mise à jour: exemple L’exemple suivant renvoie les valeurs de la ligne mise à jour et stocke le résultat dans PL / Variables SQL bnd1, bnd2, bnd3:

Le L’exemple suivant montre que vous pouvez spécifier une fonction d’agrégation à jeu unique dans l’expression de la clause de retour:

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 100 RETURNING SUM(salary) INTO :bnd1;