UPDATE (Transact-SQL)

構文

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

引数

• WITH common_table_expression
  UPDATE ステートメントのスコープ内で定義された、一時的な名前付き結果セットまたはビュー (共通テーブル式 (CTE) とも呼ばれる) を指定します。 結果セットは SELECT ステートメントから派生し、UPDATE ステートメントで参照されます。 詳細については、「WITH common_table_expression (Transact-SQL)」を参照してください。

• TOP ( expression) [ PERCENT ]
  更新する行の数または比率 (%) を指定します。expression には、行数または行の比率 (%) を指定できます。 INSERT、UPDATE、MERGE、または DELETE の TOP 式で参照される行は、任意の順序に並べられません。

  INSERT、UPDATE、MERGE、および DELETE の各ステートメントで TOP を使用する場合は、expression を区切るかっこが必要です。 詳細については、「TOP (Transact-SQL)」を参照してください。

• table_alias
  FROM 句で指定される別名です。行を更新するテーブルまたはビューを表します。

• server_name
  テーブルまたはビューが配置されているリンク サーバーの名前です。server_name は、リンク サーバー名として指定することも、OPENDATASOURCE 関数を使用して指定することもできます。

  server_name をリンク サーバーとして指定した場合は、database_name および schema_name も指定する必要があります。 server_name を OPENDATASOURCE で指定した場合は、database_name および schema_name がすべてのデータ ソースに適用されるとは限りません。これは、リモート オブジェクトにアクセスする OLE DB プロバイダーの機能に左右されます。 詳細については、「分散クエリ」を参照してください。

• database_name
  データベースの名前です。

• schema_name
  テーブルまたはビューが属するスキーマの名前です。

• table_or view_name
  行を更新するテーブルまたはビューの名前です。

  table_or_view_name によって参照されるビューは更新可能であることが必要です。また、そのビュー定義の FROM 句ではベース テーブルを 1 つだけ参照している必要があります。 更新可能なビューの詳細については、「CREATE VIEW (Transact-SQL)」を参照してください。

• rowset_function_limited
  OPENQUERY 関数または OPENROWSET 関数を指定します。 これらの関数の使用は、リモート オブジェクトにアクセスする OLE DB プロバイダーの機能により制限されます。 詳細については、「分散クエリ」を参照してください。

• WITH (table_hint_limited)
  対象になるテーブルに設定可能な 1 つ以上のテーブル ヒントを指定します。 テーブル ヒントは UPDATE ステートメントの実行中、クエリ オプティマイザーの既定の動作を上書きします。 NOLOCK および READUNCOMMITTED は使用できません。 テーブル ヒントの詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。

• @table\_variable
  table 変数をテーブル ソースとして指定します。

• SET
  更新する列名または変数名の一覧を指定します。

• column_name
  変更するデータを含む列です。column_name は、table_or view_name 内に存在している必要があります。 ID 列は更新できません。

• expression
  変数、リテラル値、式、または 1 つの値を返すかっこで囲んだサブセレクト ステートメントです。 expression で返される値は、column_name または @variable の既存の値を置き換えます。

  注意
  Unicode 文字データ型 nchar、nvarchar、および ntext を参照している場合は、"expression" の前に大文字の "N" を付ける必要があります。 "N" を指定しないと、SQL Server では、文字列はデータベースまたは列の既定の照合順序に対応するコード ページに変換されます。 文字列がこのコード ページにない場合は、失われます。 詳細については、「Unicode データの使用」を参照してください。

• DEFAULT
  列に格納された値を列に定義された既定値で置き換えることを指定します。 列に既定値が定義されておらず、NULL 値が許されている場合は、この句を使用して列を NULL に変更できます。

• { += | -= | *= | /= | %= | &= | ^= | |= }
  演算を実行し、元の値に演算の結果を設定するために使用する複合演算子です。

  +=    加算して代入

  -=    減算して代入

  *=    乗算して代入

  /=    除算して代入

  %=    剰余して代入

  &=    ビットごとの AND 演算を行って代入

  ^=    ビットごとの XOR 演算を行って代入

  |=    ビットごとの OR 演算を行って代入

  詳細については、「複合演算子 (Transact-SQL)」を参照してください。

• udt_column_name
  ユーザー定義型の列です。

• property_name | field_name
  ユーザー定義型のパブリック プロパティまたはパブリック データ メンバーです。 値を指定する式は、プロパティの型に暗黙的に変換できる必要があります。

  同一のユーザー定義型の列のプロパティを複数変更するには、複数の UPDATE ステートメントを実行するか、その型のミューテーター メソッドを呼び出します。

• method_name( argument [ ,... n] )
  1 つ以上の引数を使用する udt_column_name の静的でないパブリック ミューテーター メソッドです。ミューテーター メソッドを Transact-SQL の NULL 値で呼び出した場合や、ミューテーター メソッドにより生成された新しい値が NULL である場合、エラーが返されます。

• .WRITE (expression,@Offset, @Length)
  column_name の値のセクションを変更することを指定します。expression は、column_name の @Offset から始まる @Length 単位を置き換えます。 この句では、varchar(max)、nvarchar(max)、または varbinary(max) の列だけを指定できます。column_name では NULL 値は許容されません。また、テーブル名やテーブル別名で修飾することもできません。

  expression は、column_name にコピーされる値です。expression は、column_name 型に評価されるか、この型に暗黙的にキャストできる必要があります。 expression に NULL が設定されている場合、@Length は無視され、column_name 内の値は指定された @Offset で切り捨てられます。

  @Offset は、expression が書き込まれる、column_name 内の開始位置です。@Offset は、0 から始まる序数の位置であり、データ型は bigint で、負の数は指定できません。 @Offset が NULL の場合、更新操作により expression は既存の column_name 値の最後に追加され、@Length は無視されます。 @Offset が column_name 値の長さよりも大きい場合は、データベース エンジンによってエラーが返されます。 @Offset と @Length の和が列の基になる値の終点を超える場合、値の最後の文字までが削除されます。 @Offset と LEN(expression) の和が宣言された基になるサイズを超える場合、エラーが発生します。

  @Length は、列内のセクションの長さです。このセクションは、@Offset から始まり、expression で置き換えられます。@Length は bigint 型であり、負の数は指定できません。 @Length が NULL の場合、更新操作により column_name の値の @Offset から最後までのすべてのデータが削除されます。

  詳細については、「大きな値のデータ型を更新する」を参照してください。

• @variable
  expression で返される値を設定する、宣言された変数です。

  SET @variable = column = expression は、列と同じ値を変数に設定します。 一方、SET @variable = column, column = expression では、列の更新前の値を変数に設定します。

• OUTPUT 句
  更新されたデータまたはそれに基づく式を、UPDATE 操作の一部として返します。 OUTPUT 句は、ローカル パーティション ビュー、分散パーティション ビュー、リモート テーブル、またはリモート ビューを対象とする DML ステートメントではサポートされていません。 詳細については、「OUTPUT 句 (Transact-SQL)」を参照してください。

• FROM table_source
  別のテーブル、ビュー、または派生テーブルのソースを使用して更新操作の基になる値を提供することを指定します。 詳細については、「FROM (Transact-SQL)」を参照してください。

  更新対象のオブジェクトが FROM 句で指定されたオブジェクトと同じで、FROM 句にそのオブジェクトへの参照が 1 つしかない場合、オブジェクトの別名は指定しても指定しなくてもかまいません。 更新対象のオブジェクトが FROM 句に 2 つ以上含まれている場合、そのオブジェクトへの単独の参照でテーブルの別名を指定してはなりません。 FROM 句にあるオブジェクトへの他のすべての参照に、オブジェクトの別名を含める必要があります。

• WHERE
  更新する行を制限する条件を指定します。 WHERE 句で使用される形式に基づいて、更新には 2 種類の形式があります。

  • 検索更新では、削除する行を識別する検索条件を指定します。

  • 位置指定更新では、CURRENT OF 句を使用してカーソルを指定します。 更新操作は、カーソルの現在位置で行われます。 WHERE CURRENT OF 句を使用する位置指定更新では、カーソルの現在位置にある 1 行を更新します。 位置指定更新は、WHERE <search_condition> 句を使用して更新する行を識別する検索更新よりも正確です。 検索更新は、検索条件が特定の行を識別しない場合に複数の行を変更します。

• search_condition
  更新の対象となる行の条件を指定します。 検索条件を結合の基準条件にすることもできます。 検索条件に含まれる述語の数に制限はありません。 述語および検索条件の詳細については、「検索条件 (Transact-SQL)」を参照してください。

• CURRENT OF
  指定したカーソルの現在位置で更新を行うことを指定します。

• GLOBAL
  cursor_name でグローバル カーソルを参照することを指定します。

• cursor_name
  フェッチが行われるオープン カーソルの名前です。 cursor_name という名前のグローバル カーソルとローカル カーソルの両方があるときに、GLOBAL が指定された場合、この引数はグローバル カーソルを参照します。それ以外の場合は、ローカル カーソルを参照します。 カーソルは、更新可能である必要があります。

• cursor_variable_name
  カーソル変数の名前です。cursor_variable_name は、更新可能なカーソルを参照している必要があります。

• OPTION (query_hint [ ,... n ] )
  オプティマイザー ヒントを使用して、データベース エンジンがステートメントを処理する方法をカスタマイズすることを指定します。 詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。

ベスト プラクティス

影響を受ける古い値と新しい値を示すために、UPDATE ステートメントの中で変数名を使用することは可能です。ただし、これは UPDATE ステートメントによって影響を受けるのが単一のレコードである場合のみに限定されています。 UPDATE ステートメントで複数のレコードが影響を受ける場合は、OUTPUT 句を使用して各レコードの古い値と新しい値を返してください。

FROM 句を指定して更新操作の条件を設定するときは注意が必要です。 UPDATE ステートメントの FROM 句が、更新される各列で使用できる値を 1 つに限定していない場合 (UPDATE ステートメントが非決定的な場合)、UPDATE ステートメントの結果は未定義となります。 これにより、予期しない結果が生じる可能性があります。 たとえば、次のスクリプトの UPDATE ステートメントでは、Table1 のどちらの行も UPDATE ステートメントの FROM 句の条件を満たしています。ただし、Table1 のどちらの行を使用して 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;

FROM 句と WHERE CURRENT OF 句を組み合わせた場合にも同じ問題が発生します。 次の例では、Table2 のどちらの行も UPDATE ステートメントの FROM 句の条件を満たします。 Table2 のどちらの列を使用してテーブル 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

データ型

char 列と nchar 列は、すべて定義された長さになるまで右側に空白が埋め込まれます。

ANSI_PADDING を OFF に設定した場合、スペースだけの文字列を除いて、varchar 列および nvarchar 列に挿入したデータからは後続のスペースがすべて削除されます。 スペースだけで構成される文字列は空の文字列に切り捨てられます。 ANSI_PADDING を ON に設定すると、後続にスペースが挿入されます。 Microsoft SQL Server ODBC ドライバーおよび OLE DB Provider for SQL Server は、接続するたびに自動的に SET ANSI_PADDING を ON にします。 これは、ODBC データ ソースで構成するか、または接続属性やプロパティで設定することができます。 詳細については、「SET ANSI_PADDING (Transact-SQL)」を参照してください。

大きな値のデータ型を更新する

FILESTREAM データの更新

text 型、ntext 型、および image 型の列を更新する

UPDATE で text、ntext、または image 型の列を変更する場合、NULL で列を更新しない限り、列が初期化され、有効なテキスト ポインターが割り当てられます。また、少なくとも 1 つのデータ ページが割り当てられます。 UPDATE ステートメントで、クラスター化キーと 1 つ以上の text、ntext、または image 列の両方の更新中に、複数の行を変更する場合は、これらの列に対する部分更新は、値の完全な置き換えとして実行されます。

text、ntext、または image 型のデータの大きな部分を置換または変更するには、UPDATE ステートメントではなく WRITETEXT または UPDATETEXT ステートメントを使用してください。

重要
ntext、text、および image の各データ型は、MicrosoftSQL Server の将来のバージョンで削除される予定です。 新しい開発作業ではこれらのデータ型の使用を避け、現在このデータ型を使用しているアプリケーションは変更を検討してください。 代わりに、nvarchar(max)、varchar(max)、varbinary(max) を使用してください。 詳細については、「大きな値のデータ型の使用」を参照してください。

エラー処理

TRY…CATCH 構造でステートメントを指定することで、UPDATE ステートメントのエラー処理を実装できます。 詳細については、「Transact-SQL での TRY...CATCH の使用」を参照してください。

行の更新が制約やルールに違反する場合や、列の NULL 値の設定に違反する場合、または新しい値が互換性のないデータ型の場合には、ステートメントは取り消され、エラーが返されます。レコードは更新されません。

式の評価時に UPDATE ステートメントが算術エラー (オーバーフロー、0 による除算、ドメイン エラー) を検出した場合、更新は行われません。 バッチの残りの部分は実行されず、エラー メッセージが返されます。

クラスター化インデックスに関係する列を更新した結果、クラスター化インデックスと行のサイズが 8,060 バイトを超える場合、更新は失敗し、エラー メッセージが返されます。

相互運用性

UPDATE ステートメントをユーザー定義関数の本文で使用できるのは、変更対象のテーブルがテーブル変数の場合だけです。

INSTEAD OF トリガーが、テーブルに対する UPDATE 操作で定義されている場合は、UPDATE ステートメントの代わりにそのトリガーが実行されます。 以前のバージョンの SQL Server では、UPDATE およびその他のデータ変更ステートメントでサポートされているのは AFTER トリガーのみです。

制限事項と制約事項

FROM 句は、INSTEAD OF トリガーが定義されているビューを直接または間接的に参照する UPDATE ステートメントでは指定できません。 INSTEAD OF トリガーの詳細については、「CREATE TRIGGER (Transact-SQL)」を参照してください。

リモート テーブル、およびローカルおよびリモート パーティション ビューに対する UPDATE ステートメントの場合、SET ROWCOUNT オプションの設定は無視されます。 SQL Server の次のリリースでは、SET ROWCOUNT を使用しても UPDATE ステートメントには反映されなくなります。 新しい開発作業では UPDATE ステートメントで SET ROWCOUNT を使用するのを避け、現在 SET ROWCOUNT を使用しているアプリケーションは TOP 構文を使用するように変更してください。

共通テーブル式 (CTE) が UPDATE ステートメントの対象である場合、ステートメント内の CTE に対するすべての参照を一致させる必要があります。 たとえば、FROM 句で CTE に別名を割り当てた場合、CTE に対するすべての参照で別名を使用する必要があります。 CTE はオブジェクト ID を持たないため、CTE の参照は明確にする必要があります。SQL Server では、オブジェクト ID を使用して、オブジェクトと別名の暗黙的なリレーションシップを識別します。 このリレーションシップがない場合、クエリ プランで予期しない結合動作やクエリ結果が生成される可能性があります。 次の例では、更新操作の対象オブジェクトとして CTE を指定するときの、適切な方法と不適切な方法を示します。

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

以下に結果セットを示します。

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

以下に結果セットを示します。

ID     Value

------ -----

1      100

2      100

(2 row(s) affected)

ログ記録の動作

UPDATE ステートメントではすべてのログが記録されますが、データベースの復旧モデルに一括ログ復旧モデルまたは単純復旧モデルが設定されている場合、.WRITE 更新で新しいデータを挿入または追加する際には、最小限のログしか記録されません。 .WRITE を使用して既存の値を更新するときには、最小ログ記録は使用されません。 詳細については、「最小ログ記録が可能な操作」を参照してください。

セキュリティ

権限

例

基本構文

このセクションの例では、最低限必要な構文を使用して UPDATE ステートメントの基本機能を示します。

A. 単純な UPDATE ステートメントを使用する

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

B. 複数の列を更新する

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

更新する行を制限する

このセクションの例では、UPDATE ステートメントの影響を受ける行の数を制限するために使用できる方法を示します。

A. WHERE 句を使用する

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

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

列の値を設定する

このセクションの例では、計算値、サブクエリ、および DEFAULT 値を使用した列の更新を示します。

A. 計算値を指定する

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

B. 複合演算子を指定する

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. 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. DEFAULT 値を使用して行を更新する

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

標準的なテーブル以外の対象オブジェクトを指定する

このセクションの例では、ビュー、テーブルの別名、またはテーブル変数を指定して行を更新する方法を示します。

A. ビューを対象になるオブジェクトとして指定する

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

B. テーブルの別名を対象になるオブジェクトとして指定する

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. テーブル変数を対象になるオブジェクトとして指定する

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

他のテーブルのデータに基づいてデータを更新する

このセクションの例では、あるテーブルの行を別のテーブルの情報に基づいて更新する方法を示します。

A. 別のテーブルの情報を使用して UPDATE ステートメントを実行する

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

リモート テーブルの行を更新する

このセクションの例では、リンク サーバーまたは行セット関数を使用してリモート テーブルを参照することで、対象になるリモート テーブルの行を更新方法を示します。

A. リンク サーバーを使用してリモート テーブルのデータを更新する

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. OPENQUERY 関数を使用してリモート テーブルのデータを更新する

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

C. OPENDATASOURCE 関数を使用してリモート テーブルのデータを更新する

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

ラージ オブジェクト データ型を更新する

このセクションの例では、ラージ オブジェクト (LOB) データ型を使用して定義されている列の値を更新する方法を示します。

A. UPDATE を .WRITE と共に使用して、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. UPDATE を .WRITE と共に使用し、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. UPDATE を OPENROWSET と共に使用し、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. UPDATE を使用して FILESTREAM データを変更する

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

ユーザー定義型を更新する

次の例では、CLR ユーザー定義型 (UDT) の列の値を変更します。 ここでは 3 種類の方法を示します。 ユーザー定義列の詳細については、「CLR ユーザー定義型」を参照してください。

A. システム データ型を使用する

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

B. メソッドを呼び出す

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

C. プロパティまたはデータ メンバーの値を変更する

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

ヒントを使用してクエリ オプティマイザーの既定の動作をオーバーライドする

このセクションの例では、テーブル ヒントとクエリ ヒントを使用して、UPDATE ステートメントを処理する際のクエリ オプティマイザーの既定の動作を一時的に無効にする方法を示します。

注意
通常、SQL Server クエリ オプティマイザーでは、クエリの最適な実行プランが選択されるため、ヒントは、経験を積んだ開発者やデータベース管理者が最後の手段としてのみ使用することをお勧めします。

A. テーブル ヒントを指定する

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

B. クエリ ヒントを指定する

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

UPDATE ステートメントの結果をキャプチャする

このセクションの例では、OUTPUT 句を使用して、UPDATE ステートメントの影響を受ける各行の情報やこれらの行に基づく式を返す方法を示します。 これらの結果は、確認メッセージ、アーカイブ化、その他のアプリケーション要件で使用するために、処理アプリケーションに返すことができます。

A. UPDATE ステートメントを 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

他のステートメントで UPDATE を使用する

このセクションの例では、他のステートメントで UPDATE を使用する方法を示します。

A. UPDATE をストアド プロシージャで使用する

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. UPDATE を 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

関連項目

参照

変更履歴