UPDATE (Transact-SQL)

Modifie les données existantes d'une table ou d'une vue.

Conventions de syntaxe de Transact-SQL

Syntaxe

[ WITH <common_table_expression> [...n] ]
UPDATE 
    [ TOP ( expression ) [ PERCENT ] ] 
    { <object> | rowset_function_limited 
     [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
    }
    SET 
        { column_name = { expression | DEFAULT | NULL }
          | { udt_column_name.{ { property_name = expression 
                                | field_name = expression } 
                               | method_name ( argument [ ,...n ] ) 
                              } 
            }
          | column_name { .WRITE ( expression , @Offset , @Length ) }
          | @variable = expression 
          | @variable = column = expression [ ,...n ] 
        } [ ,...n ] 
    [ <OUTPUT Clause> ]
    [ FROM{ <table_source> } [ ,...n ] ] 
    [ WHERE { <search_condition> 
            | { [ CURRENT OF 
                  { { [ GLOBAL ] cursor_name } 
                      | cursor_variable_name 
                  } 
                ]
              }
            } 
    ] 
    [ OPTION ( <query_hint> [ ,...n ] ) ]
[ ; ]

<object> ::=
{ 
    [ server_name . database_name . schema_name . 
    | database_name .[ schema_name ] . 
    | schema_name .
    ]
        table_or_view_name}

Arguments

• WITH <common_table_expression>
  Spécifie l'ensemble de résultats ou la vue nommés temporaires, également appelés expression de table commune (CTE) et définis dans le cadre de l'instruction UPDATE. L'ensemble de résultats CTE est dérivé d'une simple requête et l'instruction UPDATE y fait référence.

  Vous pouvez également utiliser des expressions de table courantes avec les instructions SELECT, INSERT, DELETE et CREATE VIEW. Pour plus d'informations, consultez WITH common_table_expression (Transact-SQL).

• TOP ( expression**)** [ PERCENT ]
  Spécifie le nombre ou le pourcentage de lignes mises à jour. expression peut être soit un nombre, soit un pourcentage de lignes.

  Les lignes référencées dans l'expression TOP et utilisées avec les instructions INSERT, UPDATE ou DELETE ne sont pas organisées dans un ordre précis.

  Les parenthèses délimitant expression dans TOP sont nécessaires dans les instructions INSERT, UPDATE et DELETE. Pour plus d'informations, consultez TOP (Transact-SQL).

• server_name
  Nom du serveur (avec un nom de serveur lié ou la fonction OPENDATASOURCE en tant que nom de serveur) contenant la table ou la vue. Si server_name est spécifié, database_name et schema_name sont requis.

• database_name
  Nom de la base de données.

• schema_name
  Nom du schéma auquel appartient la table ou la vue.

• table_or view_name
  Nom de la table ou de la vue à partir desquelles les lignes sont mises à jour.

  Vous pouvez utiliser une variable table, dans le cadre de son étendue, en tant que source de table dans une instruction UPDATE.

  La vue, à laquelle fait référence table_or_view_name, doit pouvoir être mise à jour et faire référence exactement à une table de base dans sa clause FROM. Pour plus d'informations sur les vues pouvant être mises à jour, consultez CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Fonctions OPENQUERY ou OPENROWSET, en fonction des possibilités du fournisseur. Pour plus d'informations sur les aptitudes requises par le fournisseur, consultez Conditions associées aux instructions UPDATE et DELETE pour les fournisseurs OLE DB.

• WITH ( <Table_Hint_Limited> )
  Spécifie un ou plusieurs indicateurs de table autorisés pour une table cible. Le mot clé WITH et les parenthèses sont obligatoires. NOLOCK et READUNCOMMITTED ne sont pas autorisés. Pour plus d'informations sur les indicateurs de table, consultez Indicateur de table (T-SQL).

• SET
  Spécifie la liste des noms des colonnes ou des variables à mettre à jour.

• column_name
  Colonne qui contient les données à modifier. column_name doit exister dans table_or view_name. Il est impossible de mettre à jour les colonnes d'identité.

• expression
  Variable, valeur littérale, expression ou sous-instruction SELECT (entre parenthèses) retournant une valeur unique. La valeur retournée par expression remplace la valeur existante de column_name ou de @variable.

• DEFAULT
  Spécifie que la valeur par défaut définie pour la colonne doit remplacer la valeur actuelle de la colonne. Cet argument peut également être utilisé pour attribuer la valeur NULL à la colonne si celle-ci n'a pas de valeur par défaut et autorise les valeurs NULL.

• udt_column_name
  Colonne définie par l'utilisateur.

• property_name | field_name
  Propriété publique ou membre de données public d'un type défini par l'utilisateur.

• method_name**(**argument [ ,... n] )
  Méthode du mutateur public non statique de udt_column_name qui prend au moins un argument.

• .WRITE (expression,@Offset,@Length**)**
  Indique qu'une section de la valeur de column_name doit être modifiée. expression remplace les unités @Length à partir de @Offset de column_name. Seules les colonnes de varchar(max), nvarchar(max) ou de varbinary(max) peuvent être spécifiées avec cette clause. column_name ne peut pas avoir la valeur NULL et ne peut pas être qualifié avec un nom ou un alias de table.

  expression est la valeur copiée sur column_name. expression doit être du même type que column_name ou implicitement convertible vers celui-ci. Si expression prend la valeur NULL, @Length est ignoré, et la valeur dans column_name est tronquée au @Offset spécifié.

  @Offset est le point de départ dans la valeur de column_name sur laquelle est rédigée expression. @Offset est une position ordinale calculée à partir de zéro, de type bigint, et ne peut pas être un nombre négatif. Si la valeur de @Offset est NULL, la mise à jour ajoute expression à la fin de la valeur de column_name existante et @Length est ignoré. Si @Offset est supérieur à la longueur de la valeur de column_name, le moteur de base de données SQL Server 2005 Microsoft retourne une erreur. Si @Offset plus @Length dépassent la fin de la valeur sous-jacente dans la colonne, tout est supprimé jusqu'au dernier caractère de la valeur. Si @Offset plus LEN(expression) est supérieur à la taille sous-jacente déclarée, une erreur est générée.

  @Length est la longueur de la section dans la colonne, à partir de @Offset, remplacée par expression. @Length est du type bigint et ne peut pas être un nombre négatif. Si la valeur de @Length est NULL, la mise à jour supprime toutes les données de*@Offset* jusqu'à la fin de la valeur de column_name.

  Pour plus d'informations, consultez la section Notes.

• **@**variable
  Variable déclarée définie sur la valeur retournée par expression.

  SET **@**variable = column = expression définit la variable à la même valeur que la colonne. Ceci est différent de SET **@**variable = column, column = expression, qui définit la variable à la valeur de la colonne avant la mise à jour.

• <OUTPUT_Clause>
  Retourne des données mises à jour ou des expressions associées dans le cadre de l'opération UPDATE. La clause OUTPUT n'est pas prise en charge dans les instructions DML, qui ciblent les tables ou les vues distantes. Pour plus d'informations, consultez Clause OUTPUT (Transact-SQL).

• FROM <table_source>
  Spécifie qu'une table, une vue ou une source de table dérivée sont utilisées pour fournir les valeurs destinées à servir de critères en vue de la mise à jour. Pour plus d'informations, consultez FROM (Transact-SQL).

  Si l'objet mis à jour est le même que l'objet de la clause FROM et s'il n'existe qu'une seule référence à cet objet de la clause FROM, un alias d'objet pourra être spécifié ou non. Si l'objet mis à jour apparaît plusieurs fois dans la clause FROM, l'une des références, mais une seule, à cet objet ne doit pas spécifier un alias de la table. Toutes les autres références à l'objet dans la clause FROM doivent inclure un alias d'objet.

  Une vue avec un déclencheur INSTEAD OF UPDATE ne peut pas servir de cible à une instruction UPDATE avec une clause FROM.

• WHERE
  Spécifie les conditions de limite des lignes mises à jour. Il existe deux formes de mise à jour en fonction du contenu de la clause WHERE :

  • Les mises à jour avec recherche comportent une condition de recherche pour qualifier les lignes à supprimer.
  • Les mises à jour avec positions utilisent la clause CURRENT OF pour définir un curseur. La mise à jour se produit à l'emplacement actuel du curseur.

• <search_condition>
  Spécifie la condition à remplir pour mettre à jour les lignes. La condition de recherche peut également être la condition sur laquelle est basée une jointure. Le nombre de prédicats inclus dans une condition de recherche est illimité. Pour plus d'informations sur les prédicats et les critères de recherche, consultez Condition de recherche (Transact-SQL).

• CURRENT OF
  Spécifie que la mise à jour s'effectue à l'emplacement actuel du curseur spécifié.

• GLOBAL
  Précise que cursor_name fait référence à un curseur global.

• cursor_name
  Nom du curseur ouvert grâce auquel s'effectue l'extraction. Si un curseur global et un curseur local portent tous les deux le nom cursor_name, cet argument fait référence au curseur mondial si GLOBAL est précisé, et au curseur local dans tous les autres cas. Le curseur doit pouvoir gérer les mises à jour.

• cursor_variable_name
  Nom d'une variable de curseur. cursor_variable_name doit faire référence à un curseur autorisant les mises à jour.

• OPTION ( <query_hint> [ ,... n ] )
  Spécifie que les indicateurs d'optimiseur sont utilisés pour personnaliser le mode de traitement de l'instruction par le moteur de base de données SQL Server. Pour plus d'informations, consultez Indicateur de requête (Transact-SQL).

Notes

L'instruction UPDATE est consignée dans le journal. Cependant, les mises à jour partielles aux données de valeurs élevées utilisant la clause **.**WRITE sont journalisées de façon minimale. Pour plus d'informations, consultez la section « Mise à jour des données de valeurs élevées » ci-dessous.

Vous pouvez insérer des instructions UPDATE dans le corps des fonctions définies par l'utilisateur uniquement si la table en cours de modification est une variable table.

Si la mise à jour d'une ligne viole une contrainte ou une règle, par exemple la valeur NULL de la colonne, ou si la nouvelle valeur est d'un type de données incompatible, l'instruction est annulée, une erreur est retournée et aucun enregistrement n'est mis à jour.

Lorsqu'une instruction UPDATE rencontre une erreur arithmétique (erreur de dépassement de capacité, de division par zéro ou de domaine) lors de l'évaluation de l'expression, la mise à jour n'est pas effectuée. Le reste du lot d'instructions n'est pas exécuté et un message d'erreur est retourné.

Si la mise à jour d'une ou de plusieurs colonnes participant à un index cluster conduit à une taille d'index et de ligne supérieure à 8 060 octets, la mise à jour n'est pas effectuée et un message d'erreur est retourné.

Si l'instruction UPDATE peut modifier plusieurs lignes tout en mettant à jour la clé cluster et une ou plusieurs colonnes text, ntext ou image, la mise à jour partielle de ces colonnes est exécutée en remplaçant toutes ces valeurs.

Toutes les colonnes char et nchar sont complétées par des espaces à droite jusqu'à la longueur définie.

La définition de l'option SET ROWCOUNT est ignorée pour les instructions UPDATE en ce qui concerne les tables distantes et les vues partitionnées locales et distantes.

Si l'option ANSI_PADDING est désactivée (OFF), tous les espaces à droite sont supprimés des données insérées dans les colonnes varchar et nvarchar, sauf dans les chaînes ne contenant que des espaces. Ces chaînes sont tronquées en une chaîne vide. Si l'option ANSI_PADDING est activée (ON), des espaces supplémentaires sont insérés. Au moment de la connexion, le pilote ODBC de Microsoft SQL Server et le fournisseur OLE DB pour SQL Server attribuent automatiquement la valeur ON à SET ANSI_PADDING. Ceci peut être configuré dans les sources de données ODBC ou lors de la définition des attributs ou des propriétés des connexions. Pour plus d'informations, consultez SET ANSI_PADDING (Transact-SQL).

Une mise à jour avec position utilisant une clause WHERE CURRENT OF met à jour la ligne sur laquelle est positionné le curseur. Cette opération peut s'avérer plus précise qu'une mise à jour avec recherche utilisant une clause WHERE <search_condition> pour qualifier les lignes à mettre à jour. Une mise à jour avec recherche modifie plusieurs lignes dès lors que la condition de recherche n'identifie pas de manière unique une seule ligne.

Utilisation de l'instruction UPDATE avec la clause FROM

Les résultats d'une instruction UPDATE ne sont pas définis si celle-ci comprend une clause FROM qui ne spécifie pas qu'une seule valeur doit être disponible pour chaque occurrence de colonne mise à jour ; à savoir, si l'instruction UPDATE n'est pas déterministe. Par exemple, étant donné l'instruction UPDATE dans le script suivant, les deux lignes dans Table1 correspondent aux qualifications de la clause FROM dans l'instruction UPDATE, mais il n'y a aucune précision quant à savoir quelle ligne de Table1 est utilisée pour mettre à jour la ligne de Table2..

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
    DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1 
    (ColA int NOT NULL, ColB decimal(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2 
    (ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0);
INSERT INTO dbo.Table1 VALUES(1, 20.0);
INSERT INTO dbo.Table2 VALUES(1, 0.0);
GO
UPDATE dbo.Table2 
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2 
    INNER JOIN dbo.Table1 
    ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO
SELECT ColA, ColB 
FROM dbo.Table2;

Le même problème peut avoir lieu lors de la combinaison des deux clauses FROM et WHERE CURRENT OF. Dans cet exemple, les deux lignes de Table2 correspondent aux qualifications de la clause FROM de l'instruction UPDATE. Aucune précision n'est fournie quant à savoir quelle ligne de Table2 est utilisée pour mettre à jour la ligne de Table1.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
    DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1
    (c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);
GO
CREATE TABLE dbo.Table2
    (d1 int PRIMARY KEY NOT NULL, d2 int NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES (1, 10);
INSERT INTO dbo.Table2 VALUES (1, 20);
INSERT INTO dbo.Table2 VALUES (2, 30);
GO
DECLARE abc CURSOR LOCAL FOR
    SELECT c1, c2 
    FROM dbo.Table1;
OPEN abc;
FETCH abc;
UPDATE dbo.Table1 
SET c2 = c2 + d2 
FROM dbo.Table2 
WHERE CURRENT OF abc;
GO
SELECT c1, c2 FROM dbo.Table1;
GO

Mise à jour des colonnes définies par l'utilisateur

La mise à jour des valeurs dans des colonnes définies par l'utilisateur peut s'effectuer de l'une des façons suivantes :

• Fournir une valeur dans un type de données système SQL Server, à condition que le type défini par l'utilisateur prenne en charge la conversion de façon implicite ou explicite. L'exemple suivant indique comment mettre à jour une valeur dans une colonne définie par l'utilisateur Point, en la convertissant explicitement à partir d'une chaîne.

  UPDATE Cities
  SET Location = CONVERT(Point, '12.3:46.2')
  WHERE Name = 'Anchorage';
  

• Appeler une méthode, marquée comme mutator, d'un type défini par l'utilisateur, pour procéder à la mise à jour. L'exemple suivant appelle une méthode de mutateur du type Point appelé SetXY. L'état de l'instance de ce type est mis à jour.

  UPDATE Cities
  SET Location.SetXY(23.5, 23.5)
  WHERE Name = 'Anchorage';
  

  Remarque :
  SQL Server retourne une erreur si une méthode mutateur est appelée sur une valeur NULL Transact-SQL ou si une nouvelle valeur produite par une méthode mutateur est NULL.

• Modifier la valeur d'une propriété enregistrée ou d'un membre de données public, défini par l'utilisateur. L'expression qui fournit la valeur doit être implicitement convertible au type de propriété. L'exemple suivant modifie la valeur de propriété X du type défini par l'utilisateur Point.

  UPDATE Cities
  SET Location.X = 23.5
  WHERE Name = 'Anchorage';
  

  Pour modifier différentes propriétés d'une colonne du même type défini par l'utilisateur, vous devez émettre plusieurs instructions UPDATE ou appeler la méthode du mutateur correspondant à ce type.

Mise à jour des données de valeurs élevées

Utilisez la clause .WRITE (expression, @Offset**,**@Length) pour effectuer une mise à jour partielle ou complète des types de données varchar(max), nvarchar(max) et varbinary(max). Par exemple, une mise à jour partielle d'une colonne varchar(max) risque de supprimer ou de modifier uniquement les 200 premiers caractères de la colonne, alors qu'une mise à jour complète supprime ou modifie toutes les données de cette colonne. Les mises à jour **.**WRITE, qui insèrent ou ajoutent de nouvelles données, sont journalisées de façon minimale si le mode de récupération des bases de données est défini comme étant simple ou utilisant les journaux de transactions. La journalisation minimale n'est pas utilisée lors de la mise à jour de valeurs existantes. Pour plus d'informations, consultez Opérations journalisées minimales.

Le moteur de base de données SQL Server 2005 convertit une mise à jour partielle à une mise à jour complète lorsque l'instruction UPDATE provoque l'une des actions suivantes :

• Modification d'une colonne clé de la vue ou table partitionnée.
• Modification de plusieurs lignes et mise à jour de la clé d'un index cluster non unique sur une valeur non constante.

Vous ne pouvez pas utiliser la clause **.**WRITE pour mettre à jour une colonne NULL ou définir la valeur de column_name sur NULL.

@Offset et @Length sont spécifiés en octets pour les types de données varbinary et varchar, et en caractères pour le type de données nvarchar. Les décalages appropriés sont calculés pour les classements DBCS.

Pour optimiser les performances, nous recommandons l'insertion ou la mise à jour de données en blocs multiples de 8 040 octets.

Si dans une clause OUTPUT, il est fait référence à la colonne modifiée par la clause **.**WRITE, la valeur complète de cette colonne, que ce soit l'image avant dans **deleted.**column_name ou l'image après dans **inserted.**column_name, est retournée à la colonne spécifiée dans la variable de table. Consultez l'exemple G ci-dessous.

Pour bénéficier de la même fonctionnalité de **.**WRITE avec des données de type caractère ou binaire, utilisez STUFF (Transact-SQL).

Mise à jour des colonnes de type text, ntext et image

La modification d'une colonne text, ntext ou image avec une instruction UPDATE initialise la colonne, lui affecte un pointeur de texte valide et lui alloue au moins une page de données, sauf si la colonne est mise à jour avec des valeurs NULL.

Pour remplacer ou modifier des blocs volumineux de données text, ntext ou image, utilisez WRITETEXT ou UPDATETEXT plutôt que l'instruction UPDATE.

Important :

Les types de données ntext, text et image seront supprimés dans une prochaine version de Microsoft SQL Server. Évitez par conséquent d'utiliser ces types de données dans les nouveaux travaux de développement et envisagez de modifier les applications qui les utilisent actuellement. Utilisez à la place les types de données nvarchar(max), varchar(max) et varbinary(max). Pour plus d'informations, consultez Utilisation de types de données de valeur élevée.

Utilisation des déclencheurs INSTEAD OF sur les actions UPDATE

Lorsqu'un déclencheur INSTEAD OF est défini sur des actions UPDATE appliquées à une table, il est exécuté au lieu de l'instruction UPDATE. Les versions antérieures de SQL Server prennent uniquement en charge les déclencheurs AFTER définis sur UPDATE et autres instructions de modification de données. La clause FROM ne peut pas être spécifiée dans une instruction UPDATE qui fait référence, directement ou indirectement, à une vue sur laquelle est défini un déclencheur INSTEAD OF. Pour plus d'informations sur les déclencheurs INSTEAD OF, consultez CREATE TRIGGER (Transact-SQL).

Définition de variables et de colonnes

Vous pouvez utiliser des noms de variables dans les instructions UPDATE pour présenter les anciennes et les nouvelles valeurs affectées mais ceci n'est applicable que si l'instruction UPDATE n'affecte qu'un seul enregistrement. Si l'instruction UPDATE affecte plusieurs enregistrements pour retourner les anciennes et les nouvelles valeurs pour chacun d'eux, utilisez la clause OUTPUT.

Autorisations

Les autorisations UPDATE sont obligatoires sur la table cible. Les autorisations SELECT sont également obligatoires pour la table en cours de mise à jour si l'instruction UPDATE contient une clause WHERE ou si expression dans la clause SET utilise une colonne de la table.

Les autorisations UPDATE reviennent par défaut aux membres du rôle de serveur fixe sysadmin, aux rôles de base de données fixes db_owner et db_datawriter et au propriétaire de la table. Les membres des rôles sysadmin, db_owner et db_securityadmin, ainsi que le propriétaire de la table peuvent transférer des autorisations aux autres utilisateurs.

Exemples

A. Utilisation d'une instruction UPDATE simple

Les exemples suivants indiquent comment toutes les lignes peuvent être affectées lorsqu'une clause WHERE n'est pas utilisée pour spécifier la ou les lignes à mettre à jour.

Cet exemple met à jour les valeurs dans les colonnes Bonus, CommissionPct et SalesQuota pour toutes les lignes dans la table SalesPerson.

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL;
GO

Vous pouvez également utiliser des valeurs calculées dans une instruction UPDATE. L'exemple suivant double la valeur dans la colonne ListPrice pour toutes les lignes de la table Product.

USE AdventureWorks ;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2;
GO

B. Utilisation de l'instruction UPDATE avec une clause WHERE

L'exemple suivant utilise la clause WHERE pour spécifier quelles lignes mettre à jour. Par exemple, Adventure Works Cycles vend son modèle de bicyclette Road-250 en deux couleurs : rouge et noir. La société a décidé de modifier la couleur de ce modèle ; à la place du rouge, la bicyclette sera rouge métallisé. L'instruction suivante met à jour les lignes dans la table Production.Product pour tous les produits Road-250 rouges.

USE AdventureWorks;
GO
UPDATE Production.Product
SET Color = N'Metallic Red'
WHERE Name LIKE N'Road-250%' AND Color = N'Red';
GO

C. Utilisation de l'instruction UPDATE avec des informations provenant d'une autre table

L'exemple suivant modifie la colonne SalesYTD dans la table SalesPerson pour illustrer les dernières ventes enregistrées dans la table SalesOrderHeader.

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
    ON sp.SalesPersonID = so.SalesPersonID
    AND so.OrderDate = (SELECT MAX(OrderDate)
                        FROM Sales.SalesOrderHeader 
                        WHERE SalesPersonID = 
                              sp.SalesPersonID);
GO

L'exemple précédent suppose qu'une seule vente est enregistrée pour un vendeur particulier sur une date donnée et que les mises à jour sont actuelles. Cet exemple ne convient pas si plusieurs ventes peuvent être enregistrées pour un vendeur donné au cours d'une même journée. Il s'exécutera sans erreur, mais chaque valeur SalesYTD ne sera mise à jour qu'avec une seule vente, en dépit du nombre de ventes ayant réellement eu lieu ce jour-là. En effet, une instruction UPDATE ne met jamais une même ligne à jour à deux reprises.

Si plusieurs ventes ont lieu le même jour pour un vendeur donné, toutes les ventes de chaque vendeur doivent être additionnées dans l'instruction UPDATE, comme le montre l'exemple suivant :

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + 
    (SELECT SUM(so.SubTotal) 
     FROM Sales.SalesOrderHeader AS so
     WHERE so.OrderDate = (SELECT MAX(OrderDate)
                           FROM Sales.SalesOrderHeader AS so2
                           WHERE so2.SalesPersonID = 
                                 so.SalesPersonID)
     AND Sales.SalesPerson.SalesPersonID = so.SalesPersonID
     GROUP BY so.SalesPersonID);
GO

D. Utilisation de l'instruction avec la clause TOP

L'exemple suivant met à jour la colonne VacationHours à hauteur de 25 % pour 10 lignes aléatoires dans la table Employee.

USE AdventureWorks;
GO
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 ;
GO

E. Utilisation de l'instruction UPDATE avec la clause OUTPUT

L'exemple suivant met à jour la colonne VacationHours à hauteur de 25 % pour 10 premières lignes aléatoires dans la table Employee. La clause OUTPUT retourne la valeur de VacationHours qui existe avant l'application de l'instruction UPDATE dans la colonne DELETED.VacationHours et la valeur mise à jour dans la colonne INSERTED.VacationHours sur la variable de table@MyTableVar.

Deux instructions SELECT suivent, elle retournent les valeurs dans @MyTableVar et les résultats de la mise à jour dans la table Employee. Notez que les résultats dans la colonne INSERTED.ModifiedDate sont différents des valeurs dans la ModifiedDate colonne de la table Employee . En effet, le déclencheur AFTER UPDATE, qui met à jour la valeur de ModifiedDate à la date actuelle, est défini sur la table Employee. Cependant, les colonnes retournées à partir de OUTPUT illustrent les données avant l'activation des déclencheurs. Pour plus d'exemples qui utilisent la clause OUTPUT, consultez Clause OUTPUT (Transact-SQL).

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 
OUTPUT INSERTED.EmployeeID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

F. Utilisation de l'instruction UPDATE avec la clause WITH common_table_expression

L'exemple suivant met à jour la valeur VacationHours à hauteur de 25 % pour tous les employés qui rendent directement ou indirectement compte au ManagerID``12. L'expression de table courante retourne une liste hiérarchique des employés qui rendent compte directement au ManagerID``12 et des employés qui rendent un compte aux premiers, et ainsi de suite. Seules les lignes retournées par l'expression de table courante sont modifiées. Pour plus d'informations sur les expressions de table courantes récursives, consultez Requêtes récursives utilisant des expressions de table communes.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

G. Utilisation de l'instruction UPDATE avec la clause .WRITE pour modifier les données dans une colonne nvarchar(max)

L'exemple suivant utilise la clause **.**WRITE pour mettre à jour une valeur partielle dans DocumentSummary, une colonne nvarchar(max) dans la table Production.Document . Le terme components est remplacé par le terme features, en spécifiant le terme de remplacement, l'emplacement de départ (décalage) du terme à remplacer dans les données existantes et le nombre de caractères à remplacer (longueur). L'exemple utilise également la clause OUTPUT pour retourner les images avant et après de la colonne DocumentSummary à la variable @MyTableVartable.

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    DocumentID int NOT NULL,
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT INSERTED.DocumentID,
       DELETED.DocumentSummary, 
       INSERTED.DocumentSummary 
    INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter 
FROM @MyTableVar;
GO

H. Utilisation de l'instruction UPDATE avec la clause .WRITE pour ajouter et supprimer les données dans une colonne nvarchar(max)

Les exemples suivants ajoutent et suppriment les données d'une colonne nvarchar(max) dont la valeur est actuellement définie sur NULL. Étant donné que la clause **.**WRITE ne peut pas être utilisée pour modifier une colonne NULL, celle-ci est d'abord renseignée avec des données provisoires. Ces données sont ensuite remplacées par les données appropriées à l'aide de la clause .WRITE. Les exemples supplémentaires ajoutent des données à la fin de la valeur de colonne, suppriment (tronquent) les données de la colonne et, pour finir, suppriment les données partielles de la colonne. Les instructions SELECT affichent la modification de données générée par chaque instruction UPDATE.

USE AdventureWorks;
GO
-- Replacing NULL value with temporary data.
UPDATE Production.Document
SET DocumentSummary = N'Replacing NULL value'
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Replacing temporary data with the correct data. Setting @Length to NULL 
-- truncates all existing data from the @Offset position.
UPDATE Production.Document
SET DocumentSummary .WRITE(N'Carefully inspect and maintain the tires and crank arms.',0,NULL)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Appending additional data to the end of the column by setting 
-- @Offset to NULL.
UPDATE Production.Document
SET DocumentSummary .WRITE (N' Appending data to the end of the column.', NULL, 0)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Removing all data from @Offset to the end of the existing value by 
-- setting expression to NULL. 
UPDATE Production.Document
SET DocumentSummary .WRITE (NULL, 56, 0)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Removing partial data beginning at position 9 and ending at 
-- position 21.
UPDATE Production.Document
SET DocumentSummary .WRITE ('',9, 12)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO

I. Utilisation de l'instruction UPDATE avec OPENROWSET pour modifier une colonne varbinary(max)

L'exemple suivant remplace une image existante stockée dans une colonne varbinary(max) par une nouvelle image. La fonction OPENROWSET est utilisée en conjonction avec l'option BULK pour charger l'image sur la colonne. Cet exemple suppose qu'un fichier nommé Tires.jpg existe dans le chemin d'accès spécifié.

USE AdventureWorks;
GO
UPDATE Production.ProductPhoto
SET ThumbNailPhoto = (
    SELECT *
    FROM OPENROWSET(BULK 'c:\Tires.jpg', SINGLE_BLOB)AS x )
WHERE ProductPhotoID = 1;
GO

Voir aussi

Référence

CREATE TABLE (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
Curseurs (Transact-SQL)
DELETE (Transact-SQL)
INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Fonctions texte et image (Transact-SQL)
WITH common_table_expression (Transact-SQL)

Autres ressources

Mise à jour des données d'une table

Aide et Informations

Assistance sur SQL Server 2005