UPDATE 文の構文の簡略バージョンは次のとおりです。

UPDATE table-name 
SET column_name = expression 
WHERE search-condition

会社 Newton Ent.(SQL Anywhere サンプルデータベースの Customers テーブル内の会社) が Einstein, Inc. に吸収される場合は、次のような文を使用して会社名を更新できます。

UPDATE Customers
SET CompanyName = 'Einstein, Inc.'
WHERE CompanyName = 'Newton Ent.';

WHERE 句で任意の式を使用できます。入力された会社名のスペルがわからなければ、次のような文を使用して Newton という会社名を更新してみます。

UPDATE Customers
SET CompanyName = 'Einstein, Inc.'
WHERE CompanyName LIKE 'Newton%';

探索条件は更新されるカラムを参照する必要はありません。Newton Entertainments の会社 ID は 109 です。ID 値はテーブルのプライマリキーなので、次の文を使用して正しいローを確実に更新できます。

UPDATE Customers
SET CompanyName = 'Einstein, Inc.'
WHERE ID = 109;

SET 句は、更新されるカラムとその新しい値を指定します。WHERE 句は、更新する必要があるローを決定します。WHERE 句がない場合、指定されたすべてのローのカラムが SET 句の値によって更新されます。

SET 句で指定できる式は、定数リテラル、ホストまたは SQL 変数、サブクエリ、CURRENT TIMESTAMP などの特別値、別のテーブルから引き出された式の値、またはこれらの組み合わせです。また、SET 句で DEFAULT を指定すると、そのベーステーブルのカラムのデフォルト値を指定できます。式のデータ型が修正するカラムのデータ型と異なる場合は、可能であれば、データベースサーバーによって式はカラムの型に自動的に変換されます。変換できない場合は、データ例外が発生し、UPDATE 文は失敗します。

SET 句は、カラム値の修正以外に、変数の値を設定するために使用できます。次の例は、テーブル T の更新以外に、変数 @var に値を代入します。

UPDATE T 
SET @var = expression1, col1 = expression2
WHERE...;

これは、SELECT 文とそれに続く UPDATE を連続して実行することとほぼ同じです。

SELECT @var = expression1
FROM T
WHERE... ;
UPDATE T SET col1 = expression2
WHERE...;

UPDATE 文での変数の代入の利点は、書き込みロックを保持したまま、変数の値を文の実行の中で設定できる点です。これにより、他の接続から同時に実行される更新アクティビティによる予期しない値の代入を防ぎます。

WHERE 句は、UPDATE 文で指定したテーブルまたはテーブル式の直積に search-condition を適用することによって、更新されるローを指定します。たとえば、次の文は "One Size Fits All" を "Extra Large Tee Shirt" に書き換えます。

UPDATE Products
SET Size  = 'Extra Large'
WHERE Name = 'Tee Shirt'
   AND Size = 'One Size Fits All';

より複雑な形式の UPDATE 文では、ジョインによる更新やその他のタイプのテーブル式が可能です。

たとえば、UPDATE 文の構文 1 は次のとおりです。

UPDATE [ row-limitation ] table-name 
SET set-item[, ...]
FROM table-expression [, ...] ]
[ WHERE search-condition ]
[ ORDER BY expression [ ASC | DESC ] , ...]
[ OPTION( query-hint, ... ) ]

この形式の UPDATE 文のセマンティックは、最初に各 table-expression からのローのすべての組み合わせで構成される結果セットを計算し、次に WHERE 句の search-condition を適用してから、結果ローを ORDER BY 句を使用して順序付けします。この計算の結果は、修正されるローのセットになります。各 table-expression は、ベーステーブル、ビュー、派生テーブルのジョインで構成できます。この構文では、他のテーブルにあるカラムの値を持つ 1 つまたは複数のテーブルを更新できます。クエリオプティマイザーによって操作が並べ替えられ、UPDATE 文のより効率的な実行方式が作成される場合があります。

ベーステーブルのローが、修正されるローのセット内に複数回現れる場合、ローの新しい値が各修正試行と異なっていれば、そのローは複数回更新されます。BEFORE ROW UPDATE トリガーが存在する場合、トリガーの UPDATE OF column-list 句に応じて、BEFORE ROW UPDATE トリガーは個別のロー修正のたびに起動されます。AFTER ROW UPDATE トリガーも、トリガーの UPDATE OF column-list 句に応じて各ロー修正とともに起動されますが、ローの値が実際に変更される場合のみです。

トリガーは、トリガーのタイプと各トリガー定義の ORDER 句の値に基づいて、更新されるテーブルごとに起動されます。ただし、UPDATE 文によって複数のテーブルが修正される場合、テーブルが更新される順序は保証されません。

次の例では、Products テーブルに BEFORE ROW UPDATE トリガーおよび AFTER STATEMENT UPDATE トリガーを作成します。各トリガーによって、データベースサーバーメッセージウィンドウにメッセージが出力されます。

CREATE OR REPLACE TRIGGER trigger0
BEFORE UPDATE 
ON Products
REFERENCING OLD AS old_product NEW AS new_product 
FOR EACH ROW
BEGIN
   PRINT ('BEFORE row: PK value: ' || old_product.ID || ' New Price: ' || new_product.UnitPrice );
END;

CREATE OR REPLACE TRIGGER trigger1
AFTER UPDATE 
ON Products
REFERENCING NEW AS new_product 
FOR EACH STATEMENT
BEGIN
  DECLARE @pk INTEGER;
  DECLARE @newUnitPrice DECIMAL(12,2);
  DECLARE @err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
  DECLARE new_curs CURSOR FOR
   SELECT ID, UnitPrice FROM new_product;
  OPEN new_curs;
  LoopGetRow:
    LOOP
      FETCH NEXT new_curs INTO @pk, @newUnitPrice;
      IF SQLSTATE = @err_notfound THEN 
         LEAVE LoopGetRow
      END IF;
      PRINT ('AFTER stmt: PK value: ' || @pk || ' Unit price: ' || @newUnitPrice );
  END LOOP LoopGetRow;
  CLOSE new_curs
END;

次に、Products テーブルと SalesOrderItems テーブルのジョインに UPDATE 文を実行し、2001 年 4 月 1 日以降に出荷された、大きい注文が少なくとも 1 つある製品を 5% 割引するとします。

UPDATE Products p JOIN SalesOrderItems s ON (p.ID = s.ProductID)
SET p.UnitPrice = p.UnitPrice * 0.95
WHERE s.ShipDate > '2001-04-01' AND s.Quantity >= 72;

データベースサーバーメッセージウィンドウには、次の情報が表示されます。

BEFORE row: PK value: 700 New Price: 14.25
BEFORE row: PK value: 302 New Price: 13.30
BEFORE row: PK value: 700 New Price: 13.54
AFTER stmt: PK value: 700 Unit price: 14.25
AFTER stmt: PK value: 302 Unit price: 13.30
AFTER stmt: PK value: 700 Unit price: 13.54

メッセージは、Product 700 が 2 回更新されたことを示しています。これは、Product 700 が UPDATE 文の検索条件と一致する 2 つの異なる注文に含まれていたためです。更新の重複は、BEFORE ROW トリガーと AFTER STATEMENT トリガーの両方からわかります。各ロー修正により、各トリガー呼び出しの OLD 値および NEW 値はそれに応じて変更されます。AFTER STATEMENT トリガーでは、REFERENCING 句によって形成されるテンポラリテーブル内のローの順序は、ローが修正された順序と一致しない場合があり、それらのローの正確な順序は保証されません。

更新の重複のために、Product 700 の UnitPrice は 2 回割引され、最初の $15.00 から、$14.25 ではなく $13.54 (9.75% の割引が発生) に下がりました。この意図しない結果を回避するには、ジョインではなく EXISTS サブクエリを使用して、各 Product ローの修正が多くても 1 回になることを保証するように UPDATE 文を作成します。書き換えられた UPDATE 文では、EXISTS サブクエリと、FROM 句を許可する代替 UPDATE 文の構文の両方が使用されます。

UPDATE Products AS p 
SET p.UnitPrice = p.UnitPrice * 0.95
FROM Products AS p
WHERE EXISTS( 
    SELECT * 
    FROM SalesOrderItems s 
    WHERE p.ID = s.ProductID
       AND s.ShipDate > '2001-04-01' 
       AND s.Quantity >= 72);

実行中に UPDATE 文が参照整合性制約に違反した場合、文の動作は wait_for_commit オプションの設定によって制御されます。wait_for_commit オプションが Off に設定されていて、参照制約違反が発生した場合、UPDATE 文の結果は直ちに自動的にロールバックされ、エラーメッセージが表示されます。wait_for_commit オプションが On に設定されている場合、UPDATE 文によって発生した参照整合性制約違反は一時的に無視され、接続によって COMMIT が実行されるときにチェックされます。

修正されるベーステーブルに、プライマリキー、UNIQUE 制約、またはユニークインデックスがある場合、UPDATE 文のローごとの実行によって一意性制約違反が発生する場合があります。たとえば、テーブル T のプライマリキーカラムの値すべてを増分する次のような UPDATE 文を発行する場合があります。

UPDATE T SET PKcol = PKcol + 1;

UPDATE 文の実行中に一意性違反が発生した場合、データベースサーバーによって次の処理が自動的に行われます。

UPDATE 文の実行中に、正常に更新されるローと一時的に削除されるローは、評価の順序によって異なり、保証されません。弱い独立性レベル (独立性レベル 0、1、または 2) で実行されている他の接続からの SQL 要求の動作は、これらの一時的に削除されるローの影響を受ける場合があります。修正されるテーブルの BEFORE または AFTER ROW トリガーには、トリガーの REFERENCING 句に従って各ローの古い値と新しい値が渡されますが、修正されるテーブルに対して ROW トリガーによって個別の SQL 文が発行される場合、テンポラリテーブルに保持されているローは失われます。

UPDATE 文による各ローの修正が完了した後、テンポラリテーブルに保持されているローはベーステーブルに戻されます。一意性違反がまだ発生する場合は、UPDATE 文全体がロールバックされます。テンポラリテーブルに保持されているすべてのローがベーステーブルに正常に再挿入された場合にのみ、AFTER STATEMENT トリガーが起動されます。

修正されるベーステーブルが ON DELETE CASCADE、ON DELETE SET NULL、ON DELETE DEFAULT、ON UPDATE CASCADE、ON UPDATE SET NULL、ON UPDATE DEFAULT などの参照整合性制約アクションのターゲットである場合、ローを一時的に格納するためにデータベースサーバーによって保持テーブルは使用されません。

• UPDATE 文
• Interactive SQL での結果セット
• ansi_update_constraints オプション
• DELETE 文または UPDATE 文の整合性検査
• 更新時のロック