UPDATE (Transact-SQL)

Ändert vorhandene Daten in einer oder mehreren Spalten in einer Tabelle oder Sicht in SQL Server 2008.

Transact-SQL-Syntaxkonventionen

Syntax

[ 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
}

Argumente

• WITH common_table_expression
  Gibt das temporäre benannte Resultset oder die Sicht an, das bzw. die auch als allgemeiner Tabellenausdruck (CTE, Common Table Expression) bezeichnet wird und innerhalb der UPDATE-Anweisung definiert ist. Das Resultset wird von einer SELECT-Anweisung abgeleitet, und die UPDATE-Anweisung verweist auf dieses Resultset. Weitere Informationen finden Sie unter WITH common_table_expression (Transact-SQL).

• TOP ( expression) [ PERCENT ]
  Gibt die Anzahl oder den Prozentsatz von Zeilen an, die aktualisiert werden. expression kann entweder eine Anzahl oder ein Prozentsatz der Zeilen sein. Die Zeilen, auf die im TOP-Ausdruck einer INSERT-, UPDATE-, MERGE- oder DELETE-Anweisung verwiesen wird, sind nicht in einer bestimmten Reihenfolge angeordnet.

  In den Anweisungen INSERT, UPDATE, MERGE und DELETE sind Klammern erforderlich, die expression in TOP begrenzen. Weitere Informationen finden Sie unter TOP (Transact-SQL).

• table_alias
  Der in der FROM-Klausel angegebene Alias, der die Tabelle oder Sicht darstellt, aus der die Zeilen aktualisiert werden.

• server_name
  Der Name des Verbindungsservers, auf dem sich die Tabelle oder Sicht befindet. server_name kann als Name eines Verbindungsservers oder mithilfe der OPENDATASOURCE-Funktion angegeben werden.

  Wenn server_name als Verbindungsserver angegeben wird, sind database_name und schema_name erforderlich. Wenn server_name mit OPENDATASOURCE angegeben wird, gelten database_name und schema_name möglicherweise nicht für alle Datenquellen und unterliegen den Funktionen des OLE DB-Anbieters, der auf das Remoteobjekt zugreift. Weitere Informationen finden Sie unter Verteilte Abfragen.

• database_name
  Der Name der Datenbank.

• schema_name
  Der Name des Schemas, dem die Tabelle oder Sicht angehört.

• table_or view_name
  Der Name der Tabelle oder Sicht, aus der die Zeilen aktualisiert werden sollen.

  Die Sicht, auf die table_or_view_name verweist, muss aktualisierbar sein und auf genau eine Basistabelle in der FROM-Klausel der Sichtdefinition verweisen. Weitere Informationen zu aktualisierbaren Sichten finden Sie unter CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Die OPENQUERY-Funktion oder die OPENROWSET-Funktion. Die Verwendung dieser Funktionen unterliegt den Funktionen des OLE DB-Anbieters, der auf das Remoteobjekt zugreift. Weitere Informationen finden Sie unter Verteilte Abfragen.

• WITH (table_hint_limited)
  Gibt einen oder mehrere Tabellenhinweise an, die für die Zieltabelle zulässig sind. Tabellenhinweise überschreiben das Standardverhalten des Abfrageoptimierers für die Dauer der UPDATE-Anweisung. NOLOCK und READUNCOMMITTED sind nicht zulässig. Weitere Informationen zu Tabellenhinweisen finden Sie unter Tabellenhinweise (Transact-SQL).

• @table\_variable
  Gibt eine table-Variable als Tabellenquelle an.

• SET
  Gibt die Liste der zu aktualisierenden Spalten- oder Variablennamen an.

• column_name
  Eine Spalte, die die zu ändernden Daten enthält. column_name muss in table_or view_name vorhanden sein. Identitätsspalten können nicht aktualisiert werden.

• expression
  Eine Variable, ein Literalwert, ein Ausdruck oder eine SELECT-Anweisung als Unterabfrage in Klammern, die bzw. der einen einzigen Wert zurückgibt. Der von expression zurückgegebene Wert ersetzt den vorhandenen Wert in column_name oder @variable.

  Hinweis

  In Verweisen auf die Unicodezeichen-Datentypen nchar, nvarchar und ntext muss "expression" der Großbuchstabe "N" vorangestellt werden. Wenn "N" nicht angegeben wird, konvertiert SQL Server die Zeichenfolge in die Codepage, die der Standardsortierung der Datenbank oder Spalte entspricht. Alle Zeichen, die in der betreffenden Codepage nicht gefunden werden, gehen verloren. Weitere Informationen finden Sie unter Verwenden von Unicode-Daten.

• DEFAULT
  Gibt an, dass der vorhandene Wert in der Spalte durch den für die Spalte definierten Standardwert ersetzt werden soll. Damit kann auch die Spalte in NULL geändert werden, wenn diese keinen Standardwert aufweist und NULL-Werte zulässt.

• { += | -= | *= | /= | %= | &= | ^= | |= }
  Ein Verbundoperator, der zum Ausführen eines Vorgangs und zum Festlegen des ursprünglichen Werts auf das Ergebnis des Vorgangs verwendet wird.

  +=    Addition und Zuweisung

  -=    Subtraktion und Zuweisung

  *=    Multiplikation und Zuweisung

  /=    Division und Zuweisung

  %=    Modulo und Zuweisung

  &=    Bitweises AND und Zuweisung

  ^=    Bitweises XOR und Zuweisung

  |=    Bitweises OR und Zuweisung

  Weitere Informationen finden Sie unter Verbundoperatoren (Transact-SQL).

• udt_column_name
  Eine benutzerdefinierte Spalte.

• property_name | field_name
  Element einer öffentlichen Eigenschaft oder öffentlicher Daten eines benutzerdefinierten Typs. Der Ausdruck, der den Wert bereitstellt, muss implizit in den Typ der Eigenschaft konvertierbar sein.

  Wenn Sie verschiedene Eigenschaften einer Spalte des gleichen benutzerdefinierten Typs ändern möchten, geben Sie mehrere UPDATE-Anweisungen aus, oder rufen Sie eine Mutatormethode des Typs auf.

• method_name( argument [ ,... n] )
  Eine nicht statische, öffentliche Mutatormethode von udt_column_name, die ein oder mehrere Argumente akzeptiert. Von SQL Server wird ein Fehler zurückgegeben, wenn eine Mutatormethode für einen NULL-Wert von Transact-SQL aufgerufen wird oder wenn ein neuer von einer Mutatormethode generierter Wert NULL ist.

• .WRITE (expression,@Offset, @Length)
  Gibt an, dass ein Abschnitt des Werts von column_name geändert werden soll. expression ersetzt @Length-Einheiten, beginnend mit @Offset von column_name. Nur Spalten des Typs varchar(max), nvarchar(max) oder varbinary(max) können mit dieser Klausel angegeben werden. column_name darf nicht NULL sein und kann nicht mit einem Tabellennamen oder Tabellenalias qualifiziert werden.

  expression ist der Wert, der in column_name kopiert wird. expression muss ausgewertet oder implizit in den column_name-Typ umgewandelt werden können. Wenn expression auf NULL festgelegt wird, wird @Length ignoriert, und der Wert in column_name wird am angegebenen @Offset abgeschnitten.

  @Offset ist der Startpunkt im Wert von column_name, an dem expression geschrieben wird. @Offset ist eine nullbasierte Ordnungszahl vom Typ bigint und darf keine negative Zahl sein. Wenn @Offset NULL ist, hängt der Aktualisierungsvorgang expression an das Ende des vorhandenen column_name-Werts an, und @Length wird ignoriert. Falls @Offset größer als die Länge des column_name-Werts ist, gibt Database Engine (Datenbankmodul) einen Fehler zurück. Wenn @Offset zuzüglich @Length das Ende des zugrunde liegenden Werts in der Spalte überragt, wird der Löschvorgang bis zum letzten Zeichen des Werts durchgeführt. Wenn @Offset zuzüglich LEN(expression) größer als die zugrunde liegende deklarierte Größe ist, wird ein Fehler ausgelöst.

  @Length ist die Länge des Abschnitts in der Spalte, beginnend mit @Offset, der durch expression ersetzt wird. @Length ist vom Typ bigint und darf keine negative Zahl sein. Wenn @Length NULL ist, entfernt der Aktualisierungsvorgang bis zum Ende des column_name-Werts alle Daten aus @Offset.

  Weitere Informationen finden Sie unter "Aktualisieren von Datentypen mit umfangreichen Werten".

• @variable
  Eine deklarierte Variable, die auf den von expression zurückgegebenen Wert festgelegt wird.

  SET @variable = column = expression legt die Variable auf den gleichen Wert wie die Spalte fest. Diese Anweisung unterscheidet sich von SET @variable = column, column = expression, wodurch die Variable auf den Wert der Spalte vor der Aktualisierung festgelegt wird.

• OUTPUT_clause
  Gibt aktualisierte Daten oder Ausdrücke zurück, die darauf als Teil des UPDATE-Vorgangs basieren. Die OUTPUT-Klausel wird in DML-Anweisungen, die auf lokale partitionierte Sichten, verteilte partitionierte Sichten, Remotetabellen oder Remotesichten ausgerichtet sind, nicht unterstützt. Weitere Informationen finden Sie unter OUTPUT-Klausel (Transact-SQL).

• FROM table_source
  Gibt an, dass eine Tabelle, Sicht oder abgeleitete Tabelle als Quelle die Kriterien für den Aktualisierungsvorgang bereitstellen soll. Weitere Informationen finden Sie unter FROM (Transact-SQL).

  Wenn das Objekt, das aktualisiert wird, mit dem Objekt in der FROM-Klausel identisch und nur ein Verweis auf das Objekt in der FROM-Klausel vorhanden ist, kann wahlweise ein Objektalias angegeben werden. Wenn das Objekt, das aktualisiert wird, mehrmals in der FROM-Klausel vorhanden ist, darf genau ein Verweis auf das Objekt keinen Tabellenalias angeben. Alle anderen Verweise auf das Objekt in der FROM-Klausel müssen den Objektalias aufweisen.

• WHERE
  Gibt die Bedingungen an, mit denen die zu aktualisierenden Zeilen eingegrenzt werden. Es gibt zwei Arten von Aktualisierungen, die vom verwendeten WHERE-Klauseltyp abhängen:

  • Gesuchte Aktualisierungen legen eine Suchbedingung fest, um die zu löschenden Zeilen zu bestimmen.

  • Positionierte Aktualisierungen verwenden die CURRENT OF-Klausel, um einen Cursor anzugeben. Der Aktualisierungsvorgang wird an der aktuellen Position des Cursors ausgeführt. Eine positionierte Aktualisierung, die eine WHERE CURRENT OF-Klausel verwendet, aktualisiert die einzelne Zeile an der aktuellen Cursorposition. Dies kann genauer sein als eine gesuchte Aktualisierung, die eine WHERE <search_condition>-Klausel zur Kennzeichnung der zu aktualisierenden Zeilen verwendet. Mit einer gesuchten Aktualisierung werden mehrere Zeilen geändert, wenn eine einzelne Zeile durch die Suchbedingung nicht eindeutig identifiziert wird.

• search_condition
  Bezeichnet die Bedingung, die erfüllt sein muss, damit die Zeilen aktualisiert werden. Die Suchbedingung kann auch die Bedingung sein, auf der eine Verknüpfung basiert. Es gibt keinen Höchstwert hinsichtlich der Anzahl von Prädikaten in einer Suchbedingung. Weitere Informationen zu Prädikaten und Suchbedingungen finden Sie unter Suchbedingung (Transact-SQL).

• CURRENT OF
  Gibt an, dass die Aktualisierung an der aktuellen Position des angegebenen Cursors ausgeführt wird.

• GLOBAL
  Gibt an, dass cursor_name auf einen globalen Cursor verweist.

• cursor_name
  Der Name des geöffneten Cursors, von dem der Abruf erfolgen soll. Wenn sowohl ein globaler als auch ein lokaler Cursor namens cursor_name vorhanden sind, bezieht sich dieses Argument auf den globalen Cursor, wenn GLOBAL angegeben ist. Andernfalls bezieht es sich auf den lokalen Cursor. Der Cursor muss Aktualisierungen zulassen.

• cursor_variable_name
  Der Name der Cursorvariablen. cursor_variable_name muss auf einen Cursor verweisen, der Aktualisierungen zulässt.

• OPTION (query_hint [ ,... n ] )
  Gibt an, dass zum Anpassen der Verarbeitung der Anweisung durch Database Engine (Datenbankmodul) Hinweise für den Abfrageoptimierer verwendet werden. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL).

Bewährte Methoden

Variablennamen können in UPDATE-Anweisungen verwendet werden, um die betroffenen alten und neuen Werte anzuzeigen. Diese Vorgehensweise sollte aber nur angewendet werden, wenn die UPDATE-Anweisung einen einzelnen Datensatz betrifft. Betrifft die UPDATE-Anweisung mehrere Datensätze, verwenden Sie die OUTPUT-Klausel, um die alten und neuen Werte für die einzelnen Datensätze zurückzugeben.

Gehen Sie beim Angeben der FROM-Klausel zum Bereitstellen der Kriterien für den Aktualisierungsvorgang vorsichtig vor. Die Ergebnisse einer UPDATE-Anweisung sind nicht definiert, wenn sie nicht deterministisch ist. Dies ist der Fall, wenn die UPDATE-Anweisung eine FROM-Klausel enthält, in der nicht für jedes Vorkommen einer zu aktualisierenden Spalte genau ein Wert verfügbar ist. Dies kann zu unerwarteten Ergebnissen führen. Beispielsweise erfüllen im folgenden Skript der UPDATE-Anweisung beide Zeilen in Table1 die Bedingungen der FROM-Klausel in der UPDATE-Anweisung. Es ist jedoch nicht definiert, welche Zeile aus Table1 zum Aktualisieren der Zeile in Table2 verwendet wird.

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;

Das gleiche Problem kann auftreten, wenn die FROM-Klausel und die WHERE CURRENT OF-Klausel kombiniert werden. Im folgenden Beispiel erfüllen beide Zeilen in Table2 die Bedingungen der FROM-Klausel in der UPDATE-Anweisung. Es ist jedoch nicht definiert, welche Zeile aus Table2 zum Aktualisieren der Zeile in Table1 verwendet wird.

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

Datentypen

Alle char-Spalten und nchar-Spalten werden rechts auf die definierte Länge aufgefüllt.

Wenn ANSI_PADDING auf OFF festgelegt ist, werden alle nachfolgenden Leerzeichen aus den in varchar-Spalten und nvarchar-Spalten eingefügten Daten entfernt. Dies gilt nicht für Zeichenfolgen, die nur aus Leerzeichen bestehen. Diese Zeichenfolgen werden auf eine leere Zeichenfolge abgeschnitten. Wenn ANSI_PADDING auf ON festgelegt ist, werden nachfolgende Leerzeichen eingefügt. Der Microsoft SQL Server-ODBC-Treiber und der OLE DB-Anbieter für SQL Server legen SET ANSI_PADDING beim Herstellen einer Verbindung automatisch auf ON fest. Diese Einstellung kann in ODBC-Datenquellen oder durch Festlegen von Verbindungsattributen oder Verbindungseigenschaften konfiguriert werden. Weitere Informationen finden Sie unter SET ANSI_PADDING (Transact-SQL).

Aktualisieren von Datentypen mit umfangreichen Werten

Verwenden Sie die .WRITE (expression, @Offset,@Length)-Klausel zum Ausführen einer teilweisen oder vollständigen Aktualisierung der Datentypen varchar(max), nvarchar(max) und varbinary(max). Bei einer teilweisen Aktualisierung einer varchar(max)-Spalte werden z. B. nur die ersten 200 Zeichen der Spalte gelöscht oder geändert, während bei einer vollständigen Aktualisierung alle Daten in der Spalte gelöscht oder geändert werden.

Es wird empfohlen, Daten in Blockgrößen einzufügen bzw. zu aktualisieren, die ein Vielfaches von 8.040 Bytes sind, um eine optimale Leistung zu erzielen.

Database Engine (Datenbankmodul) konvertiert eine teilweise Aktualisierung in eine vollständige Aktualisierung, wenn die UPDATE-Anweisung eine dieser Aktionen bewirkt:

• Ändert eine Schlüsselspalte der partitionierten Sicht oder Tabelle.

• Ändert mehrere Zeilen und aktualisiert außerdem den Schlüssel eines nicht eindeutigen gruppierten Indexes in einen nicht konstanten Wert.

Sie können die .WRITE-Klausel nicht zum Aktualisieren einer NULL-Spalte oder zum Festlegen des Werts von column_name auf NULL verwenden.

@Offset und @Length werden in Byte für den varbinary-Datentyp und den varchar-Datentyp und in Zeichen für den nvarchar-Datentyp angegeben. Die geeigneten Offsets werden für Doppelbyte-Zeichensatzsortierungen (DBCS, Double-Byte Character Set) berechnet.

Wenn in einer OUTPUT-Klausel auf die von der .WRITE-Klausel geänderte Spalte verwiesen wird, wird der vollständige Wert der Spalte (entweder das Anfangsabbild in deleted.column_name oder das Endabbild in inserted.column_name) an die angegebene Spalte in der Tabellenvariablen zurückgegeben.

Verwenden Sie STUFF (Transact-SQL), um dieselbe Funktionalität von .WRITE mit anderen Zeichen- oder Binärdatentypen zu erzielen.

Aktualisieren von FILESTREAM-Daten

Sie können ein FILESTREAM-Feld mithilfe der UPDATE-Anweisung auf einen NULL-Wert, einen leeren Wert oder eine relativ kleine Menge von Inlinedaten aktualisieren. Große Datenmengen lassen sich jedoch mithilfe von Win32-Schnittstellen effizienter in eine Datei streamen. Wenn Sie ein FILESTREAM-Feld aktualisieren, ändern Sie die zugrunde liegenden BLOB-Daten im Dateisystem. Wenn ein FILESTREAM-Feld auf NULL festgelegt wird, werden die dem Feld zugeordneten BLOB-Daten gelöscht. Dabei kann .WRITE() nicht zur teilweisen Aktualisierung der FILESTREAM-Daten eingesetzt werden. Weitere Informationen finden Sie unter Übersicht über FILESTREAM.

Aktualisieren der Spalten "text", "ntext" und "image"

Durch das Ändern einer Spalte des Datentyps text, ntext oder image mit UPDATE wird die Spalte initialisiert, ein gültiger Textzeiger zugewiesen und mindestens eine Datenseite zugeordnet, es sei denn, die Spalte wird mit NULL aktualisiert. Wenn die UPDATE-Anweisung beim Aktualisieren des Gruppierungsschlüssels und mindestens einer Spalte text, ntext oder image nicht mehr als eine Zeile ändern konnte, wird die Teilaktualisierung dieser Spalten als vollständige Ersetzung der Werte ausgeführt.

Wenn Sie große Datenblöcke des Datentyps text, ntext oder image ersetzen oder ändern, verwenden Sie statt der UPDATE-Anweisung die WRITETEXT-Anweisung oder die UPDATETEXT-Anweisung.

Wichtig

Die Datentypen ntext, text und image werden in einer zukünftigen Version von MicrosoftSQL Server entfernt. Vermeiden Sie die Verwendung dieser Datentypen bei neuen Entwicklungen, und planen Sie die Änderung von Anwendungen, in denen sie gegenwärtig verwendet werden. Verwenden Sie stattdessen nvarchar(max), varchar(max) und varbinary(max). Weitere Informationen finden Sie unter Verwenden von Datentypen mit umfangreichen Werten.

Fehlerbehandlung

Sie können die Fehlerbehandlung für die UPDATE-Anweisung durch Angeben der Anweisung in einem TRY…CATCH-Konstrukt implementieren. Weitere Informationen finden Sie unter Verwenden von TRY...CATCH in Transact-SQL.

Wenn die Aktualisierung einer Zeile eine Einschränkung oder Regel oder die NULL-Einstellung für die Spalte verletzt bzw. der neue Wert einen inkompatiblen Datentyp hat, wird die Anweisung abgebrochen. Außerdem wird ein Fehler zurückgegeben, und es werden keine Datensätze aktualisiert.

Wenn in einer UPDATE-Anweisung ein arithmetischer Fehler (Überlauf, Division durch 0 (null) oder Domänenfehler) bei der Auswertung eines Ausdrucks auftritt, wird die Aktualisierung nicht ausgeführt. Der Rest des Batches wird nicht ausgeführt, und eine Fehlermeldung wird zurückgegeben.

Wenn die Aktualisierung mindestens einer Spalte eines gruppierten Index dazu führt, dass die Größe des gruppierten Index und der Zeile den Wert von 8.060 Byte überschreitet, tritt bei der Aktualisierung ein Fehler auf, und es wird eine Fehlermeldung zurückgegeben.

Interoperabilität

UPDATE-Anweisungen sind im Textkörper von benutzerdefinierten Funktionen nur zulässig, wenn es sich bei der Tabelle, die geändert wird, um eine Tabellenvariable handelt.

Wenn ein INSTEAD OF-Trigger für UPDATE-Aktionen für eine Tabelle definiert ist, wird der Trigger statt der UPDATE-Anweisung ausgeführt. Frühere Versionen von SQL Server unterstützen nur AFTER-Trigger für UPDATE-Anweisungen und andere Anweisungen zur Datenänderung.

Einschränkungen

Die FROM-Klausel kann nicht in einer UPDATE-Anweisung angegeben werden, die (direkt oder indirekt) auf eine Sicht verweist, für die ein INSTEAD OF-Trigger definiert wurde. Weitere Informationen zu INSTEAD OF-Triggern finden Sie unter CREATE TRIGGER (Transact-SQL).

Die Einstellung der SET ROWCOUNT-Option wird für UPDATE-Anweisungen ignoriert, die für Remotetabellen und partitionierte Sichten (lokal und remote) ausgeführt werden. Das Verwenden von SET ROWCOUNT wird in der nächsten Version von SQL Server keine Auswirkungen auf UPDATE-Anweisungen haben. Verwenden Sie SET ROWCOUNT bei neuen Entwicklungsarbeiten nicht zusammen mit UPDATE-Anweisungen, und ändern Sie Anwendungen, die SET ROWCOUNT derzeit verwenden, sodass sie die TOP-Syntax verwenden.

Wenn ein allgemeiner Tabellenausdruck das Ziel einer UPDATE-Anweisung ist, müssen alle Verweise auf den allgemeinen Tabellenausdruck in der Anweisung übereinstimmen. Wenn dem allgemeinen Tabellenausdruck z. B.ein Alias in der FROM-Klausel zugewiesen wird, muss der Alias für alle weiteren Verweise auf den allgemeinen Tabellenausdruck verwendet werden. Eindeutige Verweise auf allgemeine Tabellenausdrücke sind erforderlich, da ein allgemeiner Tabellenausdruck keine Objekt-ID hat, mit der SQL Server die implizite Beziehung zwischen einem Objekt und seinem Alias erkennt. Ohne diese Beziehung erzeugt der Abfrageplan möglicherweise ein unerwartetes Verknüpfungsverhalten und unbeabsichtigte Abfrageergebnisse. In den folgenden Beispielen werden richtige und falsche Methoden zum Angeben eines allgemeinen Tabellenausdrucks veranschaulicht, wenn der allgemeine Tabellenausdruck das Zielobjekt des Aktualisierungsvorgangs ist.

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

Dies ist das Resultset.

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

Dies ist das Resultset.

ID     Value

------ -----

1      100

2      100

(2 row(s) affected)

Protokollierungsverhalten

Die UPDATE-Anweisung wird vollständig protokolliert. Aktualisierungen mit .WRITE, bei denen neue Daten eingefügt oder angefügt werden, werden jedoch minimal protokolliert, wenn das Wiederherstellungsmodell für die Datenbank auf massenprotokolliert oder einfach festgelegt ist. Die minimale Protokollierung wird nicht verwendet, wenn mit .WRITE vorhandene Werte aktualisiert werden. Weitere Informationen finden Sie unter Vorgänge, für die eine minimale Protokollierung verfügbar ist.

Sicherheit

Berechtigungen

Für die Zieltabelle sind UPDATE-Berechtigungen erforderlich. SELECT-Berechtigungen sind zum Aktualisieren der Tabelle ebenfalls erforderlich, wenn die UPDATE-Anweisung eine WHERE-Klausel enthält, oder wenn expression in der SET-Klausel eine Spalte in der Tabelle verwendet.

UPDATE-Berechtigungen werden standardmäßig zu Elementen der festen Serverrollen sysadmin, db_owner und db_datawriter sowie zum Tabellenbesitzer. Mitglieder der Rollen sysadmin, db_owner und db_securityadmin sowie der Tabellenbesitzer können Berechtigungen an andere Benutzer übertragen.

Beispiele

Kategorie	Featuresyntaxelemente
Grundlegende Syntax	UPDATE
Beschränken der zu aktualisierenden Zeilen	WHERE • TOP • WITH (allgemeiner Tabellenausdruck) • WHERE CURRENT OF
Festlegen von Spaltenwerten	Berechnete Werte • Verbundoperatoren • Standardwerte • Unterabfragen
Angeben von Zielobjekten, die keine Standardtabellen sind	Sichten • Tabellenvariablen • Tabellenaliase
Aktualisieren von Daten basierend auf Daten aus anderen Tabellen	FROM
Aktualisieren von Zeilen in einer Remotetabelle	Verbindungsserver • OPENQUERY • OPENDATASOURCE
Aktualisieren von großen Objektdatentypen	.WRITE • OPENROWSET
Aktualisieren benutzerdefinierter Typen	Benutzerdefinierte Typen
Außerkraftsetzen des Standardverhaltens des Abfrageoptimierers mithilfe von Hinweisen	Tabellenhinweise • Abfragehinweise
Erfassen der Ergebnisse der UPDATE-Anweisung	OUTPUT-Klausel
Verwenden von UPDATE in anderen Anweisungen	Gespeicherte Prozeduren • TRY…CATCH

Grundlegende Syntax

Anhand von Beispielen in diesem Abschnitt wird die grundlegende Funktion der UPDATE-Anweisung mithilfe der mindestens erforderlichen Syntax veranschaulicht.

A. Verwenden einer einfachen UPDATE-Anweisung

Im folgenden Beispiel wird eine einzelne Spalte für alle Zeilen der Person.Address-Tabelle aktualisiert.

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

B. Aktualisieren mehrerer Spalten

Im folgenden Beispiel werden die Werte in den Spalten Bonus, CommissionPct und SalesQuota für alle Zeilen in der SalesPerson-Tabelle aktualisiert.

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

Beschränken der zu aktualisierenden Zeilen

Die Beispiele in diesem Abschnitt zeigen Möglichkeiten zum Beschränken der Anzahl an Zeilen auf, die von der UPDATE-Anweisung betroffen sind.

A. Verwenden der WHERE-Klausel

Im folgenden Beispiel wird die WHERE-Klausel verwendet, um die zu aktualisierenden Zeilen anzugeben. Durch die Anweisung wird der Wert in der Color-Spalte der Production.Product-Tabelle für alle Zeilen aktualisiert, die den Wert "Red" in der Color-Spalte und einen Wert, der mit "Road-250" beginnt, in der Name-Spalte enthalten.

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

B. Verwenden der TOP-Klausel

In den folgenden Beispielen wird die TOP-Klausel verwendet, um die Anzahl der Zeilen zu beschränken, die in einer UPDATE-Anweisung geändert werden. Wenn eine TOP (n)-Klausel zusammen mit UPDATE verwendet wird, wird der Aktualisierungsvorgang für eine zufällige Auswahl von n Zeilen ausgeführt. Im folgenden Beispiel wird die VacationHours-Spalte um 25 Prozent für 10 zufällige Zeilen in der Employee-Tabelle aktualisiert.

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

Wenn TOP verwendet werden muss, um Aktualisierungen in einer sinnvollen Abfolge anzuwenden, muss in einer untergeordneten SELECT-Anweisung TOP gemeinsam mit ORDER BY verwendet werden. Mit dem nachfolgenden Beispiel werden die Urlaubsstunden der 10 Mitarbeiter mit dem frühesten Einstellungsdatum aktualisiert.

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. Verwenden der WITH common_table_expression-Klausel

Im folgenden Beispiel wird für alle Mitarbeiter, die ManagerID12 direkt oder indirekt unterstellt sind, der VacationHours-Wert um 25 Prozent aktualisiert. Der allgemeine Tabellenausdruck gibt eine hierarchische Liste der Mitarbeiter zurück, die ManagerID12 direkt unterstellt sind, sowie der Mitarbeiter, die diesen Mitarbeitern unterstellt sind, usw. Es werden nur die Zeilen geändert, die vom allgemeinen Tabellenausdruck zurückgegeben werden. Weitere Informationen zu rekursiven allgemeinen Tabellenausdrücken finden Sie unter Rekursive Abfragen mithilfe von allgemeinen Tabellenausdrücken.

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. Verwenden der WHERE CURRENT OF-Klausel

Im folgenden Beispiel wird die WHERE CURRENT OF-Klausel verwendet, um ausschließlich die Zeile zu aktualisieren, auf der der Cursor positioniert ist. Wenn ein Cursor auf einer Verknüpfung basiert, wird nur die Tabelle geändert, die mit table_name in der UPDATE-Anweisung angegeben wurde. Andere im Cursor enthaltene Tabellen sind davon nicht betroffen.

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

Festlegen von Spaltenwerten

Die Beispiele in diesem Abschnitt zeigen, wie Spalten mithilfe von berechneten Werten, Unterabfragen und DEFAULT-Werten aktualisiert werden.

A. Angeben eines berechneten Werts

In den folgenden Beispielen werden berechnete Werte in einer UPDATE-Anweisung verwendet. Im Beispiel wird der Wert in der ListPrice-Spalte für alle Zeilen in der Product-Tabelle verdoppelt.

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

B. Angeben eines Verbundoperators

Im folgenden Beispiel wird die @NewPrice-Variable verwendet, um den Preis aller roten Fahrräder zu erhöhen, indem zum aktuellen Preis der Wert 10 addiert wird.

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

Im folgenden Beispiel wird der Verbundoperator "+=" verwendet, um die Daten ' - tool malfunction' an den bestehenden Wert in der Spalte Name für Zeilen anzufügen, die über eine ScrapReasonID zwischen 10 und 12 verfügen.

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

C. Angeben einer Unterabfrage in der SET-Klausel

Im folgenden Beispiel wird eine Unterabfrage in der SET-Klausel verwendet, um den Wert zu bestimmen, der zum Aktualisieren der Spalte verwendet wird. Die Unterabfrage muss einen skalaren Wert (also einen einzelnen Wert pro Zeile) zurückgeben. Im Beispiel wird die SalesYTD in der SalesPerson-Tabelle geändert, um die neuesten Verkaufszahlen in der SalesOrderHeader-Tabelle wiederzugeben. Die Unterabfrage aggregiert die Umsätze für jeden Vertriebsmitarbeiter in der UPDATE-Anweisung.

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. Aktualisieren von Zeilen mithilfe von DEFAULT-Werten

Im folgenden Beispiel wird die CostRate-Spalte auf ihren Standardwert (0.00) für alle Zeilen festgelegt, die über einen CostRate-Wert verfügen, der größer als 20.00 ist.

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

Angeben von Zielobjekten, die keine Standardtabellen sind

In den Beispielen in diesem Abschnitt wird veranschaulicht, wie Zeilen durch Angeben einer Sicht, eines Tabellenalias oder einer Tabellenvariablen aktualisiert werden.

A. Angeben einer Sicht als Zielobjekt

Im folgenden Beispiel werden Zeilen in einer Tabelle aktualisiert, indem eine Sicht als Zielobjekt angegeben wird. Die Sichtdefinition verweist auf mehrere Tabellen. Die UPDATE-Anweisung wird jedoch erfolgreich ausgeführt, da sie nur auf Spalten aus einer der zugrunde liegenden Tabellen verweist. Die UPDATE-Anweisung schlägt fehl, wenn Spalten aus beiden Tabellen angegeben werden. Weitere Informationen finden Sie unter Ändern von Daten über eine Sicht.

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

B. Angeben eines Tabellenalias als Zielobjekt

Im folgenden Beispiel werden Zeilen in der Production.ScrapReason-Tabelle aktualisiert. Der ScrapReason zugewiesene Tabellenalias in der FROM-Klausel wurde als Zielobjekt in der UPDATE-Klausel angegeben.

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. Angeben einer Tabellenvariablen als Zielobjekt

Im folgenden Beispiel werden Zeilen in einer Tabellenvariablen aktualisiert.

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

Aktualisieren von Daten basierend auf Daten aus anderen Tabellen

Anhand von Beispielen in diesem Abschnitt werden Methoden zum Aktualisieren von Zeilen aus einer Tabelle basierend auf Informationen aus einer anderen Tabelle gezeigt.

A. Verwenden der UPDATE-Anweisung mit Informationen aus einer anderen Tabelle

Im folgenden Beispiel wird die SalesYTD-Spalte in der SalesPerson-Tabelle geändert, um die neuesten Verkaufszahlen aus der SalesOrderHeader-Tabelle wiederzugeben.

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

Im vorherigen Beispiel wird angenommen, dass nur ein Verkauf für einen angegebenen Verkäufer an einem bestimmten Datum aufgezeichnet wird und Aktualisierungen aktuell sind. Wenn mehr als ein Verkauf für einen angegebenen Verkäufer am selben Tag gespeichert werden kann, funktioniert das gezeigte Beispiel nicht richtig. Das Beispiel wird zwar fehlerlos ausgeführt, jeder SalesYTD-Wert wird jedoch mit nur einem Verkauf aktualisiert. Dies gilt unabhängig davon, wie viele Verkäufe an diesem Tag tatsächlich stattgefunden haben. Die Ursache liegt darin, dass dieselbe Zeile von einer einzelnen UPDATE-Anweisung nicht zweimal aktualisiert werden kann.

Wenn für einen angegebenen Vertriebsmitarbeiter mehrere Verkäufe pro Tag möglich sind, müssen sämtliche Verkäufe der einzelnen Vertriebsmitarbeiter mithilfe der UPDATE-Anweisung aggregiert werden, wie im nachfolgenden Beispiel dargestellt:

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

Aktualisieren von Zeilen in einer Remotetabelle

In den Beispielen in diesem Abschnitt wird veranschaulicht, wie Zeilen mit einem Verbindungsserver oder einer Rowsetfunktion in einer Remotezieltabelle aktualisiert werden, um auf die Remotetabelle zu verweisen.

A. Aktualisieren von Daten in einer Remotetabelle mithilfe eines Verbindungsservers

Im folgenden Beispiel wird eine Tabelle auf einem Remoteserver aktualisiert. In diesem Beispiel wird zunächst mithilfe von sp_addlinkedserver ein Link zur Remotedatenquelle erstellt. Der Name des Verbindungsservers (MyLinkServer) wird anschließend als Teil des vierteiligen Objektnamens in der Form server.catalog.schema.object angegeben. Beachten Sie, dass für @datasrc ein gültiger Servername angegeben werden muss.

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. Aktualisieren von Daten in einer Remotetabelle mithilfe der OPENQUERY-Funktion

Im folgenden Beispiel wird durch Angabe der OPENQUERY-Rowsetfunktion eine Zeile in einer Remotetabelle aktualisiert. Der im vorherigen Beispiel erstellte Name des Verbindungsservers wird hier verwendet.

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

C. Aktualisieren von Daten in einer Remotetabelle mithilfe der OPENDATASOURCE-Funktion

Im folgenden Beispiel wird durch Angabe der OPENDATASOURCE-Rowsetfunktion eine Zeile in eine Remotetabelle eingefügt. Geben Sie mit dem Format server_name oder server_name\instance_name einen gültigen Servernamen an. Die SQL Server-Instanz muss möglicherweise für die Option Ad Hoc Distributed Queries konfiguriert werden. Weitere Informationen finden Sie unter Ad Hoc Distributed Queries (Option).

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

Aktualisieren von großen Objektdatentypen

Die Beispiele in diesem Abschnitt veranschaulichen Methoden zum Aktualisieren von Werten in Spalten, die mit großen Objektdatentypen (Large Object Data Type, LOB) definiert sind.

A. Verwenden von UPDATE mit .WRITE zum Ändern von Daten in einer nvarchar(max)-Spalte

Im folgenden Beispiel wird die .WRITE-Klausel verwendet, um einen Teilwert in DocumentSummary zu aktualisieren, einer nvarchar(max)-Spalte in der Production.Document -Tabelle. Das Wort components wird durch das Wort features ersetzt. Dazu werden das Ersetzungswort, die Anfangsposition (Offset) des zu ersetzenden Worts in den vorhandenen Daten und die Anzahl von zu ersetzenden Zeichen (Länge) angegeben. In dem Beispiel wird außerdem die OUTPUT-Klausel verwendet, um die Anfangs- und Endabbilder der Spalte DocumentSummary an die @MyTableVar-Tabellenvariable zurückzugeben.

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. Verwenden von UPDATE mit .WRITE zum Hinzufügen und Entfernen von Daten in einer nvarchar(max)-Spalte

In den folgenden Beispielen werden in einer nvarchar(max)-Spalte, die über einen derzeit auf NULL festgelegten Wert verfügt, Daten hinzugefügt bzw. Daten entfernt. Da die .WRITE-Klausel nicht zum Ändern einer NULL-Spalte verwendet werden kann, wird die Spalte zuerst mit temporären Daten aufgefüllt. Anschließend werden diese Daten mithilfe der .WRITE-Klausel durch die richtigen Daten ersetzt. In den zusätzlichen Beispielen werden am Ende des Spaltenwerts Daten angefügt, Daten (durch Abschneiden) aus der Spalte entfernt und schließlich Teildaten aus der Spalte entfernt. Die SELECT-Anweisungen zeigen die Datenänderung an, die von jeder UPDATE-Anweisung generiert wurde.

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. Verwenden von UPDATE mit OPENROWSET zum Ändern einer varbinary(max)-Spalte

Im folgenden Beispiel wird ein vorhandenes Abbild, das in einer varbinary(max)-Spalte gespeichert ist, durch ein neues Abbild ersetzt. Die OPENROWSET-Funktion wird mit der BULK-Option verwendet, um das Abbild in die Spalte zu laden. In diesem Beispiel wird angenommen, dass eine Datei namens Tires.jpg im angegebenen Dateipfad vorhanden ist.

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

D. Verwenden von UPDATE zum Ändern von FILESTREAM-Daten

Im folgenden Beispiel wird die UPDATE-Anweisung verwendet, um die Daten in der Dateisystemdatei zu ändern. Diese Methode wird nicht zum Streamen großer Datenmengen in eine Datei empfohlen. Verwenden Sie die entsprechenden Win32-Schnittstellen. Im folgenden Beispiel wird der gesamte Text im Dateidatensatz durch den Text Xray 1 ersetzt. Weitere Informationen finden Sie unter Übersicht über FILESTREAM.

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

Aktualisieren benutzerdefinierter Typen

Im folgenden Beispiel werden Werte in Spalten mit CLR-benutzerdefiniertem Typ (User Defined Type, UDT) geändert. Es werden drei Methoden gezeigt. Weitere Informationen zu benutzerdefinierten Spalten finden Sie unter Benutzerdefinierte CLR-Typen.

A. Verwenden eines Systemdatentyps

Sie können einen UDT aktualisieren, indem Sie einen Wert in einem SQL Server-Systemdatentyp bereitstellen. Voraussetzung dafür ist, dass der benutzerdefinierte Typ die implizite oder explizite Konvertierung aus diesem Typ unterstützt. Im folgenden Beispiel wird gezeigt, wie Sie einen Wert in einer Spalte des benutzerdefinierten Typs Point durch explizites Konvertieren aus einer Zeichenfolge aktualisieren.

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

B. Aufrufen einer Methode

Sie können einen UDT aktualisieren, indem Sie eine als Mutator gekennzeichnete Methode des benutzerdefinierten Typs aufrufen. Im folgenden Beispiel wird eine Mutatormethode des Typs Point mit dem Namen SetXY aufgerufen. Dadurch wird der Status der Instanz des Typs aktualisiert.

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

C. Ändern des Werts einer Eigenschaft oder eines Datenelements

Sie können einen UDT aktualisieren, indem Sie den Wert einer registrierten Eigenschaft oder eines öffentlichen Datenelements des benutzerdefinierten Typs ändern. Der Ausdruck, der den Wert bereitstellt, muss implizit in den Typ der Eigenschaft konvertierbar sein. Im folgenden Beispiel wird der Wert der X-Eigenschaft des benutzerdefinierten Typs Point geändert.

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

Außerkraftsetzen des Standardverhaltens des Abfrageoptimierers mithilfe von Hinweisen

In den Beispielen in diesem Abschnitt wird gezeigt, wie mit Tabellen- und Abfragehinweisen beim Verarbeiten der UPDATE-Anweisung zeitweise das Standardverhalten des Abfrageoptimierers außer Kraft gesetzt wird.

Vorsicht
Da der SQL Server-Abfrageoptimierer in der Regel den optimalen Ausführungsplan für eine Abfrage auswählt, wird empfohlen, dass erfahrene Entwickler und Datenbankadministratoren Hinweise nur dann verwenden, wenn sich alle anderen Möglichkeiten als unzureichend erwiesen haben.

A. Angeben eines Tabellenhinweises

Im folgenden Beispiel wird der Tabellenhinweis TABLOCK angegeben. Durch diesen Hinweis wird angegeben, dass eine freigegebene Sperre für die Production.Product-Tabelle eingerichtet und bis zum Ende der UPDATE-Anweisung aufrechterhalten wird.

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

B. Angeben eines Abfragehinweises

Im folgenden Beispiel wird der AbfragehinweisOPTIMIZE FOR (@variable) in der UPDATE-Anweisung angegeben. Dieser Hinweis weist den Abfrageoptimierer an, einen bestimmten Wert für eine lokale Variable zu verwenden, wenn die Abfrage kompiliert und optimiert wird. Dieser Wert wird nur während der Abfrageoptimierung verwendet, nicht während der Abfrageausführung.

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-%';

Erfassen der Ergebnisse der UPDATE-Anweisung

In den Beispielen in diesem Abschnitt wird gezeigt, wie mit der OUTPUT-Klausel Informationen aus jeder von einer UPDATE-Anweisung betroffenen Zeile bzw. Ausdrücke, die auf jeder Zeile basieren, zurückgegeben werden. Diese Ergebnisse können an die verarbeitende Anwendung zurückgegeben werden, die sie z. B. für Bestätigungen, Archivierungen und andere Anwendungsanforderungen verwendet.

A. Verwenden von UPDATE mit der OUTPUT-Klausel

Im folgenden Beispiel werden die ersten zehn Zeilen der VacationHours-Spalte in der Employee-Tabelle um 25 % aktualisiert. Außerdem wird der Wert in der ModifiedDate-Spalte auf das aktuelle Datum festgelegt. Die OUTPUT-Klausel gibt an die @MyTableVar-Tabellenvariable den Wert für VacationHours zurück, der vor der Anwendung der UPDATE-Anweisung in der deleted.VacationHours-Spalte vorhanden war, und den aktualisierten Wert in der inserted.VacationHours-Spalte.

Danach folgen zwei SELECT-Anweisungen, die die Werte in @MyTableVar zurückgeben, sowie die Ergebnisse des Aktualisierungsvorgangs in der Employee-Tabelle. Weitere Beispiele zum Verwenden der OUTPUT-Klausel finden Sie unter OUTPUT-Klausel (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,
    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

Verwenden von UPDATE in anderen Anweisungen

Die Beispiele in diesem Abschnitt veranschaulichen, wie UPDATE in anderen Anweisungen verwendet wird.

A. Verwenden von UPDATE in einer gespeicherten Prozedur

Im folgenden Beispiel wird eine UPDATE-Anweisung in einer gespeicherten Prozedur verwendet. Die Prozedur erfordert einen Eingabeparameter @NewHours und einen Ausgabeparameter @RowCount. Der Parameterwert @NewHours wird in der UPDATE-Anweisung verwendet, um die VacationHours-Spalte in der HumanResources.Employee-Tabelle zu aktualisieren. Der @RowCount-Ausgabeparameter wird verwendet, um die Anzahl der betroffenen Zeilen an eine lokale Variable zurückzugeben. Der CASE-Ausdruck wird in der SET-Klausel verwendet, um den Wert, der für VacationHours festgelegt wird, bedingt zu bestimmen. Wenn der Mitarbeiter pro Stunde bezahlt wird (SalariedFlag = 0), ist VacationHours auf die aktuelle Anzahl der Stunden zuzüglich des Werts festgelegt, der unter @NewHours angegeben ist. Andernfalls ist VacationHours auf den Wert festgelegt, der unter @NewHours angegeben ist.

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. Verwenden von UPDATE in einem TRY…CATCH-Block

Im folgenden Beispiel wird eine UPDATE-Anweisung in einem TRY…CATCH-Block zur Behandlung von Ausführungsfehlern verwendet, die während des Aktualisierungsvorgangs auftreten können. Weitere Informationen finden Sie unter Verwenden von TRY...CATCH in Transact-SQL.

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

Siehe auch

Verweis

DELETE (Transact-SQL)

INSERT (Transact-SQL)

MERGE (Transact-SQL)

OPENROWSET (Transact-SQL)

WITH common_table_expression (Transact-SQL)

Änderungsverlauf

Aktualisierter Inhalt
table_alias wurde als Methode zum Angeben der zu aktualisierenden Zieltabelle zur Syntax hinzugefügt.
Informationen über die Verwendung des allgemeinen Tabellenausdrucks als Ziel einer UPDATE-Anweisung wurden zum Abschnitt "Einschränkungen" hinzugefügt.