UPDATE (Transact-SQL)

Syntaxe

[ WITH common_table_expression [...n] ]
UPDATE 
    [ TOP (expression) [ PERCENT ] ] 
    { { table_alias | <object> | rowset_function_limited 
        [ WITH (table_hint_limited [ ...n ] ) ]
      }
      | @table_variable
    }
    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
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable=column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
        } [ ,...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 le jeu de résultats ou la vue nommés temporaires, également appelés « expression de table commune » (CTE) et définis dans l'étendue de l'instruction UPDATE. Le jeu de résultats est dérivé d'une instruction SELECT et est référencé par l'instruction UPDATE. Pour plus d'informations, consultez WITH common_table_expression (Transact-SQL).

• TOP ( expression) [ PERCENT ]
  Spécifie le nombre ou le pourcentage de lignes à mettre à jour. expression peut être soit un nombre, soit un pourcentage de lignes. Les lignes référencées dans l'expression TOP d'une instruction INSERT, UPDATE, MERGE ou DELETE ne sont disposées dans aucun ordre particulier.

  Les parenthèses qui entourent expression dans l'expression TOP sont obligatoires dans les instructions INSERT, UPDATE, MERGE et DELETE. Pour plus d'informations, consultez TOP (Transact-SQL).

• table_alias
  Alias spécifié dans la clause FROM représentant la table ou la vue à partir de laquelle les lignes doivent être mises à jour.

• server_name
  Nom du serveur lié sur lequel se trouve la table ou la vue. server_name peut être spécifié en tant que nom de serveur lié ou à l'aide de la fonction OPENDATASOURCE.

  Lorsque server_name est spécifié en tant que serveur lié, database_name et schema_name sont obligatoires. Lorsque server_name est spécifié avec OPENDATASOURCE, database_name et schema_name peuvent ne pas s'appliquer à toutes les sources de données ; par ailleurs, ils dépendent des fonctionnalités du fournisseur OLE DB qui accède à l'objet distant. Pour plus d'informations, consultez Requêtes distribuées.

• database_name
  Nom de la base de données.

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

• table_or view_name
  Nom de la table ou de la vue à partir de laquelle les lignes doivent être mises à jour.

  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 la clause FROM de la définition de vue. Pour plus d'informations sur les vues qu'il est possible de mettre à jour, consultez CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Fonction OPENQUERY ou OPENROWSET. L'utilisation de ces fonctions dépend des fonctionnalités du fournisseur OLE DB qui accède à l'objet distant. Pour plus d'informations, consultez Requêtes distribuées.

• WITH (table_hint_limited)
  Spécifie un ou plusieurs indicateurs de table autorisés pour la table cible. Les indicateurs de table prennent le pas sur le comportement par défaut de l'optimiseur de requête pendant la durée de l'instruction UPDATE. NOLOCK et READUNCOMMITTED ne sont pas autorisés. Pour plus d'informations sur les indicateurs de table, consultez Indicateurs de table (Transact-SQL).

• @table\_variable
  Spécifie une variable de table comme source de table.

• 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 instruction de sous-sélection (entre parenthèses) retournant une valeur unique. La valeur retournée par expression remplace la valeur existante de column_name ou de @variable.

  [!REMARQUE]

  Lorsque vous faites référence aux types de données de caractères Unicode nchar, nvarchar et ntext, l'expression doit comporter la majuscule « N » en préfixe. Si vous ne spécifiez pas « N », SQL Server convertit la chaîne en page de codes qui correspond au classement par défaut de la base de données ou de la colonne. Tous les caractères absents de cette page de codes sont perdus. Pour plus d'informations, consultez Utilisation de données Unicode.

• 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.

• { += | -= | *= | /= | %= | &= | ^= | |= }
  Opérateur composé utilisé pour exécuter une opération et affecter la valeur d'origine au résultat de l'opération.

  +=    Ajouter et attribuer

  -=    Soustraire et attribuer

  *=    Multiplier et attribuer

  /=    Diviser et attribuer

  %=    Modulo et attribuer

  &=    AND au niveau du bit et attribuer

  ^=    XOR au niveau du bit et attribuer

  |=    OR au niveau du bit et attribuer

  Pour plus d'informations, consultez Opérateurs composés (Transact-SQL).

• udt_column_name
  Colonne de type défini par l'utilisateur.

• property_name | field_name
  Propriété publique ou membre de données public d'un type défini par l'utilisateur. L'expression qui fournit la valeur doit être implicitement convertible au type de propriété.

  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.

• method_name( argument [ ,... n] )
  Méthode du mutateur public non statique de udt_column_name qui prend au moins un argument. 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.

• .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 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 a 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 écrite 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 column_name, le moteur de base de données 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 « Mise à jour des types de données de valeur élevée ».

• @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. Cela 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 les données mises à jour ou des expressions basées sur ces données dans le cadre de l'opération UPDATE. La clause OUTPUT n'est pas prise en charge dans les instructions DML qui ciblent des vues partitionnées locales, des vues partitionnées distribuées, des tables distantes ou des 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 qu'il n'existe qu'une seule référence à cet objet dans la clause FROM, un alias d'objet pourra être, ou non, spécifié. Si l'objet mis à jour apparaît plusieurs fois dans la clause FROM, une références, et 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 l'alias d'objet.

• 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. Une mise à jour positionnée utilisant une clause WHERE CURRENT OF met à jour uniquement 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.

• 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 global 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. Pour plus d'informations, consultez Indicateurs de requête (Transact-SQL).

Meilleures pratiques

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, utilisez la clause OUTPUT pour retourner les anciennes et les nouvelles valeurs pour chacun d'eux.

Utilisez l'avertissement lors de la spécification de la clause FROM pour fournir les valeurs destinées à servir de critères en vue de la mise à jour. 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. Ceci peut engendrer des résultats inattendus. 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 PRIMARY KEY 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), (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), (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

Types de données

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

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. Lors de chaque 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).

Mise à jour des types de données de valeur élevée

Mise à jour de données FILESTREAM

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

Gestion des erreurs

Vous pouvez implémenter la gestion des erreurs pour l'instruction UPDATE en spécifiant cette dernière dans une construction TRY…CATCH. Pour plus d'informations, consultez Utilisation de TRY...CATCH dans Transact-SQL.

Si la mise à jour d'une ligne enfreint une contrainte ou une règle, qu'elle viole le paramètre NULL de la colonne ou que 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 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é.

Interopérabilité

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 de table.

Lorsqu'un déclencheur INSTEAD OF est défini sur des actions UPDATE appliquées à une table, il est exécuté à la place 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.

Limitations et restrictions

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).

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. L'utilisation de SET ROWCOUNT n'affectera en rien les instructions UPDATE dans la prochaine version de SQL Server. N'utilisez pas SET ROWCOUNT avec les instructions UPDATE dans les nouvelles tâches de développement et modifiez les applications qui utilisent actuellement la syntaxe TOP.

Lorsqu'une expression de table commune (CTE) est la cible d'une instruction UPDATE, toutes les références cette expression de table commune dans l'instruction doivent correspondre. Par exemple, si un alias est affecté à l'expression de table commune dans la clause FROM, cet alias doit être utilisé pour toutes les autres références à l'expression de table commune. Des références non ambiguës à l'expression de table commune sont requises, car une expression de table commune n'a pas d'ID d'objet, lequel est utilisé par SQL Server pour identifier la relation implicite entre un objet et son alias. Sans cette relation, le plan de requête peut avoir un comportement de jointure et des résultats de requête inattendus. Les exemples suivants illustrent l'utilisation de méthodes correctes et incorrectes en matière de spécification d'une expression de table commune lorsque celle-ci est l'objet cible de l'opération de mise à jour.

USE tempdb;
GO
-- UPDATE statement with CTE references that are correctly matched.
DECLARE @x TABLE (ID int, Value int);
DECLARE @y TABLE (ID int, Value int);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);

WITH cte AS (SELECT * FROM @x)
UPDATE x -- cte is referenced by the alias.
SET Value = y.Value
FROM cte AS x  -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID;
SELECT * FROM @x;
GO

Voici l'ensemble des résultats.

ID     Value

------ -----

1      100

2      200

(2 row(s) affected)

-- UPDATE statement with CTE references that are incorrectly matched.
USE tempdb;
GO
DECLARE @x TABLE (ID int, Value int);
DECLARE @y TABLE (ID int, Value int);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);

WITH cte AS (SELECT * FROM @x)
UPDATE cte   -- cte is not referenced by the alias.
SET Value = y.Value
FROM cte AS x  -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID; 
SELECT * FROM @x; 
GO

Voici l'ensemble des résultats.

ID     Value

------ -----

1      100

2      100

(2 row(s) affected)

Comportement de journalisation

L'instruction UPDATE est entièrement journalisée ; cependant, 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 lorsque .WRITE sert à mettre à jour des valeurs existantes. Pour plus d'informations, consultez Opérations pouvant faire l'objet d'une journalisation minimale.

Sécurité

Autorisations

Exemples

Syntaxe de base

Les exemples fournis dans cette section présentent les fonctionnalités de base de l'instruction UPDATE en utilisant la syntaxe minimale requise.

A. Utilisation d'une instruction UPDATE simple

USE AdventureWorks;
GO
UPDATE Person.Address
SET ModifiedDate = GETDATE();

B. Mise à jour de plusieurs colonnes

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

Limitation des lignes mises à jour

Les exemples fournis dans cette section illustrent les moyens que vous pouvez utiliser pour limiter le nombre de lignes affectées par l'instruction UPDATE.

A. Utilisation de la clause WHERE

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

B. Utilisation de la clause TOP

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

UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 EmployeeID FROM HumanResources.Employee
     ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.EmployeeID = th.EmployeeID;
GO

C. Utilisation de la clause WITH common_table_expression

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

D. Utilisation de la clause WHERE CURRENT OF

USE AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.EmployeeID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.EmployeeID = b.EmployeeID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2 
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

Définition des valeurs de colonne

Les exemples fournis dans cette section illustrent la mise à jour de colonnes à l'aide des valeurs calculées, sous-requêtes et valeurs DEFAULT.

A. Spécification d'une valeur calculée

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

B. Spécification d'un opérateur composé

USE AdventureWorks;
GO
DECLARE @NewPrice int = 10;
UPDATE Production.Product
SET ListPrice += @NewPrice
WHERE Color = N'Red';
GO

USE AdventureWorks;
GO
UPDATE Production.ScrapReason 
SET Name += ' - tool malfunction'
WHERE ScrapReasonID BETWEEN 10 and 12;

C. Spécification d'une sous-requête dans la clause SET

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. Mise à jour de lignes à l'aide de valeurs DEFAULT

USE AdventureWorks;
GO
UPDATE Production.Location
SET CostRate = DEFAULT
WHERE CostRate > 20.00;

Spécification d'objets cibles autres que les tables standard

Les exemples présentés dans cette section montrent comment mettre à jour des lignes en spécifiant une vue, un alias de table ou une variable de table.

A. Spécification d'une vue comme objet cible

USE AdventureWorks;
GO
UPDATE Person.vStateProvinceCountryRegion
SET CountryRegionName = 'United States of America'
WHERE CountryRegionName = 'United States';

B. Spécification d'un alias de table comme objet cible

USE AdventureWorks;
GO
UPDATE sr
SET sr.Name += ' - tool malfunction'
FROM Production.ScrapReason AS sr
JOIN Production.WorkOrder AS wo 
     ON sr.ScrapReasonID = wo.ScrapReasonID
     AND wo.ScrappedQty > 300;

C. Spécification d'une variable de table comme objet cible

USE AdventureWorks;
GO
-- Create the table variable.
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    NewVacationHours int,
    ModifiedDate datetime);

-- Populate the table variable with employee ID values from HumanResources.Employee.
INSERT INTO @MyTableVar (EmpID)
    SELECT EmployeeID FROM HumanResources.Employee;

-- Update columns in the table variable.
UPDATE @MyTableVar
SET NewVacationHours = e.VacationHours + 20,
    ModifiedDate = GETDATE()
FROM HumanResources.Employee AS e 
WHERE e.EmployeeID = EmpID;

-- Display the results of the UPDATE statement.
SELECT EmpID, NewVacationHours, ModifiedDate FROM @MyTableVar
ORDER BY EmpID;
GO

Mise à jour de données selon des données d'autres tables

Les exemples fournis dans cette section présentent des méthodes de mise à jour de lignes d'une table selon les informations d'une autre table.

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

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

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

Mise à jour de lignes dans une table distante

Les exemples présentés dans cette section montrent comment mettre à jour des lignes dans une table cible distante en utilisant un serveur lié ou une fonction d'ensemble de lignes pour référencer la table distante.

A. Mise à jour de données dans une table distante en utilisant un serveur lié

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI10', 
    @datasrc = N'<server name>',
    @catalog = N'AdventureWorks';
GO
USE AdventureWorks;
GO
-- Specify the remote data source using a four-part name 
-- in the form linked_server.catalog.schema.object.

UPDATE MyLinkServer.AdventureWorks.HumanResources.Department
SET GroupName = N'Public Relations'
WHERE DepartmentID = 4;

B. Mise à jour de données dans une table distante en utilisant la fonction OPENQUERY

UPDATE OPENQUERY (MyLinkServer, 'SELECT GroupName FROM HumanResources.Department WHERE DepartmentID = 4') 
SET GroupName = 'Sales and Marketing';

C. Mise à jour de données dans une table distante en utilisant la fonction OPENDATASOURCE

UPDATE OPENQUERY (MyLinkServer, 'SELECT GroupName FROM HumanResources.Department WHERE DepartmentID = 4') 
SET GroupName = 'Sales and Marketing';

Mise à jour de types de données d'objet volumineuses

Les exemples fournis dans cette section présentent des méthodes de mise à jour de valeurs dans les colonnes définies avec les types de données d'objet volumineuses (LOB).

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

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

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

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

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

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

D. Utilisation de l'instruction UPDATE pour modifier des données FILESTREAM

UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' as varbinary(max))
WHERE [SerialNumber] = 2;

Mise à jour de types définis par l'utilisateur

Les exemples suivants modifient des valeurs dans les colonnes de type clr défini par l'utilisateur. Trois méthodes sont illustrées. Pour plus d'informations sur les colonnes définies par l'utilisateur, consultez Types CLR définis par l'utilisateur.

A. Utilisation d'un type de données système

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

B. Appel d'une méthode

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

C. Modification de la valeur d'une propriété ou d'un membre de données

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

Substitution du comportement par défaut de l'optimiseur de requête à l'aide d'indicateurs

Les exemples présentés dans cette section montrent comment utiliser des indicateurs de table et de requête pour substituer temporairement le comportement par défaut de l'optimiseur de requête lors du traitement de l'instruction UPDATE.

Attention
Étant donné que l'optimiseur de requête SQL Server sélectionne généralement le meilleur plan d'exécution pour une requête, nous vous recommandons de ne recourir aux indicateurs qu'en dernier ressort et seulement si vous êtes un développeur ou un administrateur de base de données expérimenté.

A. Spécification d'un indicateur de table

USE AdventureWorks;
GO
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

B. Spécification d'un indicateur de requête

USE AdventureWorks;
GO
CREATE PROCEDURE Production.uspProductUpdate
@Product nvarchar(25)
AS
SET NOCOUNT ON;
UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE @Product
OPTION (OPTIMIZE FOR (@Product = 'BK-%') );
GO
-- Execute the stored procedure 
EXEC Production.uspProductUpdate 'BK-%';

Capture des résultats de l'instruction UPDATE

Les exemples présentés dans cette section montrent comment utiliser la clause OUTPUT pour retourner des informations de chaque ligne affectée par une instruction UPDATE, ou des expressions basées sur ces lignes. Ces résultats peuvent être retournés à l'application en cours de traitement afin d'être utilisés notamment avec des messages de confirmation, des opérations d'archivage et d'autres spécifications d'application similaires.

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

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,
    ModifiedDate = GETDATE() 
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.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Utilisation de l'instruction UPDATE dans d'autres instructions

Les exemples présentés dans cette section montrent comment utiliser UPDATE dans d'autres instructions.

A. Utilisation de l'instruction UPDATE dans une procédure stockée

USE AdventureWorks;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint,
@RowCount int OUTPUT
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
SET @RowCount = @@ROWCOUNT;
GO

-- Execute the stored procedure and return the number of rows updated to the variable @RowCount
DECLARE @RowCount int;
EXECUTE HumanResources.Update_VacationHours 40, @RowCount OUTPUT;
SELECT @RowCount AS RowCount;

B. Utilisation de l'instruction UPDATE dans un bloc TRY…CATCH

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Intentionally generate a constraint violation error.
    UPDATE HumanResources.Department
    SET Name = N'MyNewName'
    WHERE DepartmentID BETWEEN 1 AND 2;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Voir aussi

Référence

Historique des modifications