UPDATE は、テーブルの行を変更する DML ステートメントです。

UPDATE ステートメントは、WITH 句で始まり、UPDATE 内でアクセス可能な共通テーブル式を定義できます。 セクション13.2.15「WITH (共通テーブル式)」を参照してください。

単一テーブル構文:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

複数テーブル構文:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

単一テーブル構文の場合、UPDATE ステートメントは、指定されたテーブル内の既存の行のカラムを新しい値に更新します。 SET 句は、変更するカラムと、それらのカラムに指定される値を示します。 各値は式か、またはカラムを明示的にそのデフォルト値に設定するキーワード DEFAULT として指定できます。 WHERE 句 (指定されている場合) は、どの行を更新するかを識別する条件を指定します。 WHERE 句がない場合は、すべての行が更新されます。 ORDER BY 句が指定されている場合は、指定されている順序で行が更新されます。 LIMIT 句は、更新できる行数に制限を設定します。

複数テーブル構文の場合、UPDATE は、条件を満たす table_references で指定されている各テーブル内の行を更新します。 一致した各行は、条件に複数回一致した場合でも、1 回更新されます。 複数テーブル構文の場合は、ORDER BY および LIMIT を使用できません。

パーティション化されたテーブルの場合は、このステートメントの単一テーブルと複数テーブルの両方の形式で、テーブル参照の一部としての PARTITION オプションの使用がサポートされます。 このオプションは、1 つ以上のパーティションまたはサブパーティション (またはその両方) のリストを受け取ります。 リストされているパーティション (またはサブパーティション) だけが一致をチェックされ、これらのパーティションまたはサブパーティションのいずれにも存在しない行は、where_condition を満たすかどうかにかかわらず更新されません。

詳細および例については、セクション24.5「パーティション選択」を参照してください。

where_condition は、更新される各行に対して true に評価される式です。 式の構文については、セクション9.5「式」を参照してください。

table_references と where_condition は、セクション13.2.10「SELECT ステートメント」で説明されているように指定されます。

実際に更新された、UPDATE 内で参照されているカラムに対してのみ UPDATE 権限が必要です。 読み取られるが、変更されないカラムに対しては、SELECT 権限のみが必要です。

UPDATE ステートメントは、次の修飾子をサポートします。

ORDER BY 句を持つステートメントを含む UPDATE IGNORE ステートメントには、ステートメントベースのレプリケーションに対して安全でないというフラグが付けられます。 (これは、どの行が無視されるかが、行が更新される順序によって決定されるためです。) このようなステートメントは、ステートメントベースのモードの使用時にエラーログに警告を生成し、MIXED モードの使用時に行ベースの形式を使用してバイナリログに書き込まれます。 (Bug #11758262、Bug #50439) 詳細は、セクション17.2.1.3「バイナリロギングでの安全および安全でないステートメントの判断」を参照してください。

式で更新されるテーブルのカラムにアクセスする場合、UPDATE はそのカラムの現在の値を使用します。 たとえば、次のステートメントは、col1 をその現在の値より 1 大きい値に設定します。

UPDATE t1 SET col1 = col1 + 1;

次のステートメントの 2 番目の割り当ては、col2 を元の col1 値ではなく、現在の (更新された) col1 値に設定します。 この結果、col1 と col2 の値が同じになります。 この動作は標準 SQL とは異なります。

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

単一テーブルの UPDATE の割り当ては一般に、左から右に評価されます。 複数テーブルの更新では、割り当てが特定の順序で実行される保証はありません。

カラムをその現在の値に設定した場合は、MySQL がこれに気付き、その更新を行いません。

NOT NULL として宣言されているカラムを NULL に設定することによって更新すると、厳密な SQL モードが有効になっている場合は、エラーが発生します。そうでない場合、カラムはそのカラムデータ型の暗黙のデフォルト値に設定され、警告数が 1 増やされます。 暗黙のデフォルト値は、数値型では 0、文字列型では空の文字列 ('')、および日付と時間型では「0」の値です。 セクション11.6「データ型デフォルト値」を参照してください。

生成されたカラムが明示的に更新される場合、許可される値は DEFAULT のみです。 生成されるカラムの詳細は、セクション13.1.20.8「CREATE TABLE および生成されるカラム」 を参照してください。

UPDATE は、実際に変更された行数を返します。 mysql_info() C API 関数は、一致して更新された行数と、UPDATE 中に発生した警告の数を返します。

LIMIT row_count を使用すると、UPDATE のスコープを制限できます。 LIMIT 句は、一致した行の制限です。 このステートメントは、実際に変更されたかどうかにかかわらず、WHERE 句を満たす row_count 行を見つけるとすぐに停止します。

UPDATE ステートメントに ORDER BY 句が含まれている場合は、この句で指定されている順序で行が更新されます。 これは、通常であればエラーが発生する可能性のある特定の状況で役立つ場合があります。 テーブル t に、一意のインデックスを持つカラム id が含まれているとします。 次のステートメントは、行が更新される順序によっては、重複キーエラーで失敗する可能性があります。

UPDATE t SET id = id + 1;

たとえば、このテーブルの id カラムに 1 と 2 が含まれており、2 が 3 に更新される前に 1 が 2 に更新された場合は、エラーが発生します。 この問題を回避するには、大きな id 値を持つ行が小さな値を持つ行の前に更新されるように、ORDER BY 句を追加します。

UPDATE t SET id = id + 1 ORDER BY id DESC;

また、複数のテーブルを範囲に含む UPDATE 操作を実行することもできます。 ただし、複数テーブルの UPDATE では ORDER BY または LIMIT を使用できません。 table_references 句は、結合に含まれるテーブルをリストします。 その構文については、セクション13.2.10.2「JOIN 句」で説明されています。 次に例を示します。

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

前の例は、カンマ演算子を使用する内部結合を示していますが、複数テーブルの UPDATE ステートメントは、SELECT ステートメント内で許可されている任意の型の結合 (LEFT JOIN など) を使用できます。

外部キー制約が存在する InnoDB テーブルを含む、複数テーブルの UPDATE ステートメントを使用した場合は、MySQL オプティマイザが、それらの親子関係の順序とは異なる順序でテーブルを処理する可能性があります。 この場合、このステートメントは失敗し、ロールバックされます。 代わりに、1 つのテーブルを更新したあと、InnoDB が提供する ON UPDATE 機能を使用して、ほかのテーブルがそれに応じて変更されるようにします。 セクション13.1.20.5「FOREIGN KEY の制約」を参照してください。

テーブルを更新してサブクエリーの同じテーブルから直接選択することはできません。 これを回避するには、複数テーブルの更新を使用します。この更新では、いずれかのテーブルが実際に更新するテーブルから導出され、エイリアスを使用して導出テーブルを参照します。 次に示すステートメントを使用して定義された items という名前のテーブルを更新するとします:

CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);

値入れが 30% 以上で在庫が 100 未満のアイテムの小売価格を下げるには、WHERE 句でサブクエリーを使用する次のような UPDATE ステートメントを使用します。 次に示すように、このステートメントは機能しません:

mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

かわりに、次のように、最も外側の WHERE 句でエイリアスを使用して参照し、サブクエリーを更新対象のテーブルのリストに移動する複数テーブル更新を使用できます:

UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

オプティマイザはデフォルトで導出テーブル discounted を最も外側のクエリーブロックにマージしようとするため、導出テーブルを強制的に実体化する場合にのみ機能します。 これを行うには、更新を実行する前に optimizer_switch システム変数の derived_merge フラグを off に設定するか、次のように NO_MERGE オプティマイザヒントを使用します:

UPDATE /*+ NO_MERGE(discounted) */ items,
       (SELECT id FROM items
        WHERE retail / wholesale >= 1.3 AND quantity < 100)
        AS discounted
    SET items.retail = items.retail * 0.9
    WHERE items.id = discounted.id;

このような場合にオプティマイザヒントを使用する利点は、オプティマイザヒントが使用されるクエリーブロック内でのみ適用されるため、UPDATE の実行後に optimizer_switch の値を再度変更する必要がないことです。

次のように、IN または EXISTS を使用しないようにサブクエリーをリライトすることもできます:

UPDATE items,
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;

この場合、サブクエリーはマージではなくデフォルトで実体化されるため、導出テーブルのマージを無効にする必要はありません。