UPDATE

テーブル内の既存のデータを変更します。

構文

UPDATE
        {
        
table_name WITH ( < table_hint_limited > [ ...n ] )
         | view_name
    
    | rowset_function_limited
        }
        SET

        { column_name = { expression | DEFAULT | NULL }
        | @variable = expression
        | @variable = column = expression } [ ,...n ]

    { { [ FROM { < table_source > } [ ,...n ] ]

        [ WHERE
            < search_condition > ] }
        |
        [ WHERE CURRENT OF

        { { [ GLOBAL ] cursor_name } | cursor_variable_name }
        ] }
        [ OPTION
( < query_hint > [ ,...n ] ) ]

< table_source > ::=
    table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
    | view_name [ [ AS ] table_alias ]
    | rowset_function [ [ AS ] table_alias ]
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
    | < joined_table >

< joined_table > ::=
    < table_source > < join_type > < table_source > ON < search_condition >
    | < table_source > CROSS JOIN < table_source >
    | < joined_table >

< join_type > ::=
    [ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ]
    [ < join_hint > ]
    JOIN

< table_hint_limited > ::=
    
{    FASTFIRSTROW
        | HOLDLOCK
        | PAGLOCK
        | READCOMMITTED
        | REPEATABLEREAD
        | ROWLOCK
        | SERIALIZABLE
        | TABLOCK
        | TABLOCKX
        | UPDLOCK
    }

< table_hint > ::=
    
{    INDEX ( index_val [ ,...n ] )
        | FASTFIRSTROW
        | HOLDLOCK
        | NOLOCK
        | PAGLOCK
        | READCOMMITTED
        | READPAST
        | READUNCOMMITTED
        | REPEATABLEREAD
        | ROWLOCK
        | SERIALIZABLE
        | TABLOCK
        | TABLOCKX
        | UPDLOCK
    }

< query_hint > ::=
    {    {HASH | ORDER} GROUP
        | { CONCAT | HASH | MERGE } UNION
        | {LOOP | MERGE | HASH } JOIN
        | FAST number_rows
        | FORCE ORDER
        | MAXDOP

        | ROBUST PLAN
        | KEEP PLAN
    }

引数

table_name

更新するテーブルの名前です。テーブルが現在のサーバーまたはデータベースにないか、または現在のユーザーがオブジェクトを所有していない場合、名前をリンク サーバー、データベース、および所有者名で修飾することができます。

WITH ( < table_hint_limited > [ ...n ] )

対象のテーブルに設定可能なテーブル ヒントを指定します。キーワード WITH とかっこが必要になります。READPAST、NOLOCK、および READUNCOMMITTED は指定できません。詳細については、「FROM」を参照してください。

view_name

更新するビューの名前です。view_name が参照するビューは、更新可能でなければなりません。UPDATE ステートメントによって加えた変更は、ビューの FROM 句で参照されるベース テーブルの 2 つ以上には影響しません。更新可能なビューの詳細については、「CREATE VIEW」を参照してください。

rowset_function_limited

プロバイダの機能によって、OPENQUERY 関数、または OPENROWSET 関数を指定します。プロバイダに必要な機能の詳細については、「」を参照してください。行セット関数の詳細については、「OPENQUERY」および「OPENROWSET」を参照してください。

SET

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

column_name

変更するデータを含む列です。column_name は、UPDATE 句で指定したテーブルまたはビューに存在していなければなりません。ID 列は更新できません。

列名を修飾して限定した場合、UPDATE 句のテーブル名またはビュー名と修飾子が一致している必要があります。たとえば、次の指定は有効です。

UPDATE authors
    SET authors.au_fname = 'Annie'
    WHERE au_fname = 'Anne'

FROM 句で指定したテーブルの別名を SET column_name で修飾子として使用することはできません。たとえば、次の指定は無効です。

UPDATE titles
    SET t.ytd_sales = t.ytd_sales + s.qty
    FROM titles t, sales s
    WHERE t.title_id = s.title_id
    AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

例を実行するには、別名 t. を列名から削除します。

UPDATE titles
    SET ytd_sales = t.ytd_sales + s.qty
    FROM titles t, sales s
    WHERE t.title_id = s.title_id
    AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

expression

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

DEFAULT

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

@variable

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

SET @variable = column = expression は、列と同じ値を変数に設定します。これは、更新前の列の値に変数を設定する SET @variable = column, column = expression とは異なります。

FROM <table_source>

別のテーブルを使用して更新操作の元となる値を提供することを指定します。詳細については、「FROM」を参照してください。

table_name [[AS] table_alias ]
更新操作の元となる値を提供するテーブルの名前です。

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

view_name [ [AS] table_alias ]
更新操作の元となる値を提供するビューの名前です。INSTEAD OF UPDATE トリガを伴うビューは、FROM 句を伴う UPDATE の対象にはなりません。
WITH ( < table_hint > [ ...n ] )
元のテーブルに 1 つ以上のテーブル ヒントを指定します。テーブル ヒントの詳細については、このマニュアルの「FROM」を参照してください。
rowset_function [ [ AS ] table_alias ]
関数の名前およびオプションの別名です。行セット関数の一覧については、「行セット関数」を参照してください。
derived_table
データベースから行を取得するサブクエリです。derived_table は 1 つ上のレベルのクエリの入力として使用されます。
column_alias
結果セット内の列名に対する別名です。このパラメータは省略可能です。選択リストの各列の別名を 1 つずつ含みます。列の別名リスト全体をかっこで囲みます。

<joined_table>

2 つ以上のテーブルが組み合わされた結果セットです。たとえば、次のようになります。

SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
    RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
        ON tab3.c1 = tab4.c1
        ON tab2.c3 = tab4.c3

複数の CROSS 結合については、かっこを使って結合の順序を変更できます。

<join_type>

結合演算子の種類を指定します。

INNER
一致する行のすべてを返すことを指定します。両方のテーブルで一致しない行は廃棄します。結合タイプを指定しない場合は、これが既定値になります。
LEFT
[OUTER]
内部結合で返されるすべての行に加えて、指定された条件に合わない左のテーブルのすべての行を、結果セットに含むことを指定します。右のテーブルからの出力列は NULL に設定されます。
RIGHT [OUTER]
内部結合で返されるすべての行に加えて、指定された条件に合わない右のテーブルのすべての行を、結果セットに含むことを指定します。左のテーブルからの出力列は NULL に設定されます。
FULL [OUTER]
左または右のテーブルからの行が選択条件に一致しない場合は、その行を結果セットに含み、その他のテーブルに対応する出力列を NULL に設定することを指定します。これは、内部結合で通常返されるすべての行に追加されるものです。
<join_hint>
結合ヒントまたは実行アルゴリズムを指定します。<join_hint> を指定した場合は、INNER、LEFT、RIGHT、または FULL も明示的に指定する必要があります。詳細については、「FROM」を参照してください。
JOIN
指定のテーブルまたはビューを結合する必要があることを示します。

ON <search_condition>

結合するときの条件を指定します。列と比較演算子はよく使用されますが、条件で任意の結合述語を指定できます。たとえば、次のようになります。

FROM Suppliers JOIN Products
    ON (Suppliers.SupplierID = Products.SupplierID)

列に結合条件を指定する場合、使用される列は、同じ列名、同じデータ型である必要はありません。ただし、データ型が異なる場合は、互換性のあるデータ型であるか、Microsoft® SQL Server™ が自動的に変換できるデータ型である必要があります。データ型が自動的に変換されない場合は、CAST 関数を使用して明示的に変換する必要があります。

検索条件および述語の詳細については、「Search Condition」を参照してください。

CROSS JOIN

2 つのテーブルの結合を指定します。結合するテーブルが FROM 句に単純にリストされ、WHERE 句が指定されていない場合と同じ行が返されます。

WHERE

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

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

  • 位置指定更新では、CURRENT OF 句を使用してカーソルを指定します。更新操作は、カーソルの現在位置で行われます。

<search_condition>

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

CURRENT OF

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

GLOBAL

cursor_name がグローバル カーソルを意味することを指定します。

cursor_name

フェッチを行う、オープンされたカーソルの名前です。cursor_name と同じ名前のグローバル カーソルとローカル カーソルの両方が存在する場合、GLOBAL の指定があれば、cursor_name はグローバル カーソルを意味します。GLOBAL を指定しない場合、cursor_name はローカル カーソルを意味します。カーソルは、更新可能である必要があります。

cursor_variable_name

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

OPTION ( < query_hint > [ ,...n ] )

オプティマイザ ヒントを使用して SQL Server のステートメント処理をカスタマイズすることを指定します。

{ HASH | ORDER } GROUP
クエリの GROUP BY または COMPUTE 句で指定した集計で、ハッシュ演算または順序付けを使用することを指定します。
{ LOOP | MERGE | HASH |} JOIN
クエリ全体で、すべての結合操作がループ結合、マージ結合、またはハッシュ結合によって実行されることを指定します。結合ヒントを複数指定した場合は、可能なヒントの中から最も負荷の軽いストラテジがクエリ オプティマイザによって選択されます。同じクエリの中で、特定のテーブルのペアに対しても結合ヒントが指定されている場合は、2 つのテーブルの結合よりもこちらの指定が優先されます。
{ MERGE | HASH | CONCAT } UNION
UNION セットのマージ、ハッシュ演算、または連結を行うことにより、すべての UNION 演算を実行することを指定します。2 つ以上の UNION ヒントを指定した場合、クエリ オプティマイザは指定されたヒントの中から最もコストのかからない方法を選択します。

  FROM 句で、結合したテーブルの特定の組に対して結合ヒントも指定した場合は、OPTION 句で指定した結合ヒントよりもこの指定が優先します。


FAST number_rows
最初の number_rows (負でない整数) を高速検索するためにクエリの最適化を行うことを指定します。最初の number_rows を返した後、クエリは実行を続け、完全な結果セットを作成します。
FORCE ORDER
クエリの最適化時にクエリの構文で指定された結合順を保持することを指定します。
MAXDOP number
このオプションを指定しているクエリに対してのみ、sp_configuremax degree of parallelism 設定オプションを上書きします。MAXDOP クエリ ヒントを使用している場合は、max degree of parallelism 設定オプションで使用されるすべての意味規則を適用できます。詳細については、「」を参照してください。
ROBUST PLAN
パフォーマンスが低下しても、最大行サイズになる可能性のあるプランを強制的にクエリ オプティマイザに実行させます。このようなプランの実行が不可能な場合、クエリ オプティマイザはエラーの検出よりクエリの実行を優先するのではなく、エラーを返します。行が可変長列で構成されている可能性があります。SQL Server では、SQL Server が処理できる範囲を超えた最大可能サイズを持つように、行を定義できます。通常、可能な最大サイズに関係なく、アプリケーションは SQL Server の処理能力で実際に対応できるサイズの行を格納します。SQL Server が長すぎる行を検出した場合は、実行エラーが返されます。

KEEP PLAN

クエリ オプティマイザに対して、クエリの予想再コンパイルしきい値を緩和することを指定します。予想再コンパイルしきい値とは、予想した回数のインデックス列変更 (更新、削除、挿入など) がテーブルに加えられた場合に、クエリを自動的に再コンパイルする時点のことです。KEEP PLAN を指定することによって、テーブルに複数の更新が加えられた場合に、クエリが再コンパイルされる頻繁が低くなります。

解説

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

table 変数は、その有効範囲の中で、通常のテーブルのようにアクセスできます。したがって、table 変数は、UPDATE ステートメントで、データを更新するテーブルとして使用できます。

4 つの部分から構成され、サーバー名の部分が OPENDATASOURCE 関数の名前は、UPDATE ステートメントでテーブル名を指定できるすべての場所でテーブル ソースとして使用できます。

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

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

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

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

クラスタ化キーと、1 つ以上の textimage、または Unicode 列の両方を更新するときに、更新クエリが 2 行以上の行を変更する可能性がある場合、更新は失敗し、SQL Server はエラー メッセージを返します。

UPDATE で textntext、または image 列を変更する場合、NULL で列を更新しない限り、列が初期化され、有効なテキスト ポインタが割り当てられます。また、少なくとも 1 つのデータ ページが割り当てられます。

  UPDATE ステートメントはログをとります。textntext、または image 型の大きなデータ ブロックを置き換えるか、または変更する場合は、UPDATE ステートメントではなく、WRITETEXT または UPDATETEXT ステートメントを使用します。WRITETEXT ステートメントも UPDATETEXT ステートメントも (既定では) ログをとりません。

char 列および nchar 列は、すべて定義された長さになります。

リモート テーブル、およびローカルおよびリモート分割ビューに対する UPDATE ステートメントの場合、SET ROWCOUNT オプションの設定は無視されます。

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

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

UPDATEステートメントの FROM 句を指定する方法が、更新対象の各列に対して使用できる値を 1 つに限定していない場合 (UPDATE ステートメントが非決定的の場合)、UPDATE ステートメントの結果は未定義となります。たとえば、次のスクリプトの UPDATE ステートメントでは、テーブル s のどちらの行も UPDATE ステートメントの FROM 句の条件を満たしています。ただし、テーブル s のどちらの行を使用してテーブル t の行を更新するかは定義していません。

CREATE TABLE s (ColA INT, ColB DECIMAL(10,3))
GO
CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3))
GO
INSERT INTO s VALUES(1, 10.0)
INSERT INTO s VALUES(1, 20.0)
INSERT INTO t VALUES(1, 0.0)
GO
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
GO

FROM 句と WHERE CURRENT OF 句を組み合わせると、同じ問題が起きることがあります。この例では、テーブル t2 のどちらの列も UPDATE ステートメントの FROM 句の条件を満たします。テーブル t2 のどちらの列を使用してテーブル t1 の行を更新するかは定義していません。

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT)
GO
CREATE TABLE t2(d1 INT PRIMARY KEY, d2 INT)
GO
INSERT INTO t1 VALUES (1, 10)
INSERT INTO t2 VALUES (1, 20)
INSERT INTO t2 VALUES (2, 30)
go

DECLARE abc CURSOR LOCAL FOR
SELECT * FROM t1

OPEN abc

FETCH abc

UPDATE t1 SET c2 = c2 + d2
FROM t2
WHERE CURRENT OF abc
GO
変数と列を設定する

UPDATE ステートメントで生じた変更の古い値と新しい値を表示するのに、変数名を使用できます。これは、UPDATE ステートメントが 1 つのレコードに影響する場合にだけ使用してください。UPDATE ステートメントが複数のレコードに影響する場合、変数には更新された行のうち 1 行の値だけが含まれます。

権限

INSERT 権限は、特に指定のない限り固定サーバー ロール sysadmin、固定データベース ロール db_owner、および固定データベース ロール db_datawriter のメンバと、テーブル所有者に与えられます。sysadmindb_owner、および db_securityadmin の各ロールのメンバ、およびテーブル所有者は、権限をほかのユーザーに譲渡できます。

UPDATE ステートメントで WHERE 句を指定する場合や、SET 句の expression でテーブル内の列を使用する場合は、SELECT 権限も必要です。

A.    簡単な UPDATE を使用する

これらの例は、UPDATE ステートメントで WHERE 句を指定しないと、すべての行が影響を受けることを示しています。

この例では、publishers テーブルに登録されたすべての出版社がジョージア州アトランタに本社を移転した場合の publishers テーブルの更新方法を示します。

UPDATE publishers
SET city = 'Atlanta', state = 'GA'

この例では、すべての出版社名を NULL に変更しています。

UPDATE publishers
SET pub_name = NULL

更新するときに算出した値を使用することもできます。この例では、titles テーブルに登録されたすべての価格を 2 倍にしています。

UPDATE titles
SET price = price * 2
B.    WHERE 句を伴った UPDATE ステートメントを使用する

WHERE 句は、更新する行を指定します。たとえば、カリフォルニア北部の名前を Pacifica (短縮名 PC) に変更し、オークランドの住民が都市名を Bay City に変更することを投票で認めたと仮定します。この例では、元の住所が無効になるオークランド住民について authors テーブルを更新する方法を示します。

UPDATE authors
   SET state = 'PC', city = 'Bay City'
      WHERE state = 'CA' AND city = 'Oakland'

カリフォルニア北部のほかの市の住民データの州名を変更する場合は、新たなステートメントを書く必要があります。

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

この例では、sales テーブルの最新の売上高を反映するように titles テーブルの ytd_sales 列を変更します。

UPDATE titles
   SET ytd_sales = titles.ytd_sales + sales.qty
      FROM titles, sales
         WHERE titles.title_id = sales.title_id
         AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

この例では、指定日に登録されている指定の書籍の売り上げが 1 件しかなく、それを最新情報に更新すると仮定しています。この条件に当てはまらない場合 (同じ日に同じ書籍の売り上げが 2 件以上登録されている場合) は、この例は正しく更新を行いません。この場合、エラーなしで実行されますが、実際に同じ日に登録された売り上げ件数に関係なく、各書籍につき 1 件の売り上げしか更新されません。これは、1 つの UPDATE ステートメントで同じ行を 2 回更新できないためです。

同じ日に同じ書籍の売り上げが 2 件登録されるような場合は、次の例のように、UPDATE ステートメントの中で各書籍ごとにすべての売り上げを集計する必要があります。

UPDATE titles
   SET ytd_sales =
      (SELECT SUM(qty)
         FROM sales
            WHERE sales.title_id = titles.title_id
            AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales))
   FROM titles, sales
D.    SELECT ステートメントで TOP 句を指定した UPDATE を使用する

この例では、authors テーブルの最初の 10 人の著者について state 列を更新します。

UPDATE authors
SET state = 'ZZ'
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id

関連項目

CREATE INDEX

CREATE TABLE

CREATE TRIGGER

カーソル

DELETE

INSERT

SET ROWCOUNT

テキストとイメージ関数

表示: