INSERT OR UPDATE (SQL)

単一行の挿入または更新

• INSERT OR UPDATE table (column, column2, ...) VALUES (value, value2, ...) 指定したテーブルの列に、値の行を挿入するか、この値の行を更新します。VALUES 節の値は、列のリスト内の列の名前と位置的に対応する必要があります。単一行の挿入または更新では、%ROWCOUNT 変数が 1 に設定され、%ROWID 変数が挿入される行または更新される行に設定されます。

  この文は、まず新しいデータの行を Sample.Records テーブルに挿入しようとします。RecordID 列が UNIQUE 制約を適用し、挿入される RecordID が既に存在する場合、INSERT OR UPDATE は代わりにその既存の行を更新します。

  INSERT OR UPDATE Sample.Records (RecordID,StatusDate,Status) VALUES (105,'05/12/22','Purged')

  

  例 ： テーブルに行を挿入するか、これを更新する

• INSERT OR UPDATE table VALUES (value, value2, ...) は、テーブルの値の行を列番号順に挿入するか、これを更新します。データ値は、定義されている列リストと位置的に対応する必要があります。指定可能なすべてのテーブル列に値を指定する必要があります。定義済みの既定値を使用することはできませんが、空の文字列を値として指定することはできます。RowID 列は指定可能ではないため、RowID 値は VALUES リストに含めないでください。

  以下の文は、まず、4 つの値を持つ行を順に Sample.Address テーブルに挿入しようとします。この列の組み合わせに一意制約があり、このキーの値が既にテーブルで定義されている場合、INSERT OR UPDATE は代わりに既存の行を更新します。

  INSERT OR UPDATE Sample.Address VALUES ('22 Main St.','Anytown','PA','65342')

  

• INSERT OR UPDATE table SET column = value, column2 = value2, ... は、特定の列の値を明示的に設定することにより、値の行を挿入または更新します。

  以下の文は、INSERT OR UPDATE table (column, column2, ...) VALUES (value, value2, ...) 構文と同じ操作を実行します。

  INSERT OR UPDATE Sample.Records SET RecordID=105, StatusDate='05/12/22',Status='Purged'

  

• INSERT OR UPDATE table DEFAULT VALUES は、既定の列値のみを含む行を挿入または更新します。

  • 定義済みの既定値を持つ列は、その値に設定されます。

  • 定義された既定値がない列は、NULL に設定されます。

  以下の文は、既定の列値を持つ行を Sample.Person テーブルに挿入します。

  INSERT OR UPDATE Sample.Person DEFAULT VALUES

  

• INSERT OR UPDATE table VALUES :array() は、ホスト変数として指定された配列の値を、テーブルの列に挿入するか、これを更新します。この構文は、埋め込み SQL でのみ使用できます。この配列の値は、暗黙的に、列番号順に行の列に対応している必要があります。指定可能な各列に値を指定する必要があります。列順を使用する INSERT OR UPDATE では、定義された列の既定値を取ることはできません。

  このクラス・メソッドは、埋め込み SQL を使用して、配列を Sample.FullName テーブルに挿入するか、これを更新します。myarray(1) は、RowID 列用に予約されているため、指定されません。

  ClassMethod EmbeddedSQLInsertOrUpdateHostVarArray()
  {
    set myarray(2)="Juanita"
    set myarray(3)="Pybus"
    &sql(INSERT OR UPDATE Sample.FullName VALUES :myarray())
    if SQLCODE '= 0 {
      write !, "Insert or update failed, SQLCODE= ", SQLCODE, ! ,%msg
      quit
    }
    write !,"Insert or update succeeded" quit
  }

  

  ホスト変数と配列については、"添え字付き配列としてのホスト変数" を参照してください。

複数行の挿入または更新

• INSERT OR UPDATE table query は、SELECT クエリの結果セットから取得したデータの行を挿入または更新します。結果セット内の列は、テーブル内の列と一致する必要があります。INSERT OR UPDATE を SELECT と共に使用することで、別のテーブルから抽出した既存のデータをテーブルに移入できます。

  この文は、Sample.Customer テーブルの Name 行を Sample.Person テーブルに挿入するか、Sample.Person の既存の行を対応する Sample.Customer の値で更新します。

  INSERT OR UPDATE Sample.Person SELECT Name FROM Sample.Customer

  

• INSERT OR UPDATE table (column, column2, ...) query は、クエリ結果セットのデータ行を、指定された列に挿入するか、この行を更新します。

  以下の文は、Sample.Person の Name および DOB 列のクエリ結果セットのデータを、Sample.Kids テーブルの一致する列に挿入するか、このデータを更新します。

  INSERT OR UPDATE Sample.Kids (Name,DOB) SELECT Name,DOB FROM Sample.Person WHERE Age <= 18

  

挿入または更新のオプション

• INSERT OR UPDATE INTO table ... は、オプションの INTO キーワードを指定します。

• INSERT OR UPDATE %keyword [INTO] table ... は、1 つまたは複数の %keyword オプションを空白で区切って設定します。有効なオプションは、%NOCHECK、%NOFPLAN、%NOINDEX、%NOJOURN、%NOLOCK、%NOTRIGGER、%PROFILE、および %PROFILE_ALL です。

  Note:

  %NOCHECK キーワードを使用すると一意の値のチェックが無効になるため、INSERT OR UPDATE %NOCHECK は常に挿入操作になり、INSERT と等価になります。

table

挿入を実行するテーブルまたはビューの名前。この引数はサブクエリでもかまいません。

column

列名、または列名のコンマ区切りのリスト。後者の場合は、値のリストに対応した順序で指定します。省略した場合、値のリストは列番号順にすべての列に適用されます。

IDKEY 値は挿入はできますが、更新はできません。これらの制約の詳細は、"IDKEY 列値" を参照してください。

value

column 内の対応する列のデータ値を指定する、VALUES 節で指定されるスカラ式またはコンマ区切りのスカラ式のリスト。指定した値の数が列数より少ない場合、SQLCODE -62 エラーが生成されます。指定した値の数が列数より多い場合、SQLCODE -116 エラーが生成されます。

INSERT OR UPDATE には INSERT と同じ値制約があります。詳細は、"INSERT" コマンドの "value" 引数を参照してください。

array

ホスト変数として指定する値の動的なローカル配列。この値は、埋め込み SQL にのみ適用されます。

配列の最下位の添え字は指定しないでください。:myupdates()、:myupdates(5,)、および :myupdates(1,1,) はすべて、有効な指定になります。

query

その結果セットが、column で指定された対応する列にデータ値を指定する SELECT クエリ。

SELECT クエリは 1 つ以上のテーブルから列データを抽出し、INSERT OR UPDATE コマンドはこの列データを含むテーブル内に、対応する新しい行を作成します。挿入されるデータがテーブル列に収まることができる限り、対応する列は、さまざまな列名および列長を持つことができます。対応する列がデータ型および長さ検証チェックに合格しない場合、InterSystems SQL は SQLCODE -104 エラーを生成します。

SELECT と共に INSERT OR UPDATE を使用すると、%ROWCOUNT 変数は挿入または更新した行の数に設定されます (0 または正の整数)。

%keyword

INSERT OR UPDATE 処理を構成するキーワード・オプション。キーワード・オプションは順不同で指定できます。複数のキーワード・オプションは、スペースで区切ります。

以下のキーワードを指定できます。

• %NOCHECK — 一意の値のチェックおよび外部キーの参照整合性チェックを無効にします。INSERT OR UPDATE の更新操作を無効にします。

• %NOFPLAN — この操作の凍結プランを無視し、新しいクエリ・プランを生成します。

• %NOINDEX — INSERT OR UPDATE 処理の際にインデックス・マップの設定を無効にします。

• %NOJOURN — 挿入操作の間、ジャーナリングを抑制し、トランザクションをオフにします。

• %NOLOCK — INSERT OR UPDATE の実行時に行のロックを無効にします。

• %NOTRIGGER — INSERT OR UPDATE の処理時にベース・テーブル挿入トリガはかけません。

• %PROFILE、%PROFILE_ALL — INSERT OR UPDATE 文のパフォーマンス分析統計 (SQLStats) を生成します。

  • %PROFILE はメイン・クエリ・モジュールに対して SQLStats を収集します。

  • %PROFILE_ALL はメイン・クエリ・モジュールとそのすべてのサブクエリ・モジュールに対して SQLStats を収集します。

これらのキーワードの詳細は、"INSERT" コマンドの "keyword" 引数を参照してください。

テーブルに行を挿入するか、これを更新する

この例では、新しいテーブル (SQLUser.CaveDwellers) を作成し、INSERT を使用してテーブルにデータを移入し、さらに INSERT OR UPDATE を使用して行を追加し、既存の行を更新します。

主キーとして指定された列 Num を持つテーブルを作成します。この制約により、列値は強制的に NULL ではなく一意となります。

CREATE TABLE SQLUser.CaveDwellers (
Num INTEGER PRIMARY KEY,
CaveCluster CHAR(80) NOT NULL,
Troglodyte CHAR(50) NOT NULL)

INSERT OR UPDATE 文を使用してテーブルに 3 つの行を挿入し、SELECT * を使用してそのテーブル・データを表示します。これらの行は以前は存在していなかったため、INSERT OR UPDATE は、これらすべてに対して挿入操作を実行します。

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (1,'Bedrock','Flintstone,Fred')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (2,'Bedrock','Flintstone,Wilma')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (3,'Bedrock','Flintstone,Pebbles')

SELECT * FROM SQLUser.CaveDwellers

追加の 4 つのデータ行を挿入または更新します。

• 最初の 3 つの文では、主キー列 Num に挿入される値はまだテーブルに存在していないため、INSERT OR UPDATE は挿入操作を実行します。

• 最後の文では、Num 列値として 3 が既にテーブルに存在するため、INSERT OR UPDATE は更新操作を実行します。INSERT OR UPDATE は Troglodyte 列をその行の新しい値で更新します。

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (4,'Bedrock','Rubble,Barney')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (5,'Bedrock','Rubble,Betty')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (6,'Bedrock','Rubble,Bamm-Bamm')

INSERT OR UPDATE SQLUser.CaveDwellers (Num,CaveCluster,Troglodyte) VALUES (3,'Bedrock','Flintstone-Rubble,Pebbles')

SELECT * FROM SQLUser.CaveDwellers

完了したら、テーブルを削除します。

DROP TABLE SQLUser.CaveDwellers

一意性チェック

INSERT OR UPDATE は、UNIQUE 列値を既存のデータ値に突き合わせることによって、行があるかどうかを判別します。UNIQUE 制約の違反が生じた場合、INSERT OR UPDATE は更新操作を実行します。UNIQUE 列値は、INSERT OR UPDATE で明示的に指定することも、列の既定値または計算された値の結果とすることもできます。

シャード・テーブルに対して INSERT OR UPDATE が実行されると、シャード・キー が UNIQUE KEY 制約と同じか、そのサブセットである場合、INSERT OR UPDATE は更新操作を実行します。

別の一意の値 (シャード・キーではない) が見つかったために INSERT OR UPDATE が更新を試みると、一意制約違反により、SQLCODE -120 エラーでこのコマンドが失敗します。

カウンタ列

INSERT OR UPDATE を実行する場合、InterSystems IRIS は最初に、操作は挿入であると想定します。したがって、SERIAL (%Library.CounterOpens in a new tab) 列に整数を指定するために使用される内部カウンタを 1 ずつインクリメントします。挿入では、これらのインクリメントされたカウンタ値を使用して、これらの列に整数値を割り当てます。ただし、InterSystems IRIS は、更新操作を行う必要があると判断した場合には、INSERT OR UPDATE は既に内部カウンタをインクリメントしていますが、これらのインクリメントされた整数値をカウンタ列に割り当てません。このため、次の操作が挿入の場合、これらの列の整数シーケンスにギャップが生じます。詳細は、以下の例を参照してください。

1. 内部カウンタ値は 4 です。INSERT OR UPDATE は、内部カウンタをインクリメントし、次に行 5 を挿入します (内部カウンタ=5、SERIAL 列値=5)。

2. INSERT OR UPDATE は、内部カウンタをインクリメントし、次に既存の行に対して更新を実行する必要があると判断します (内部カウンタ=6、列カウンタの変更なし)。

3. INSERT OR UPDATE は、内部カウンタをインクリメントし、次に行を挿入します (内部カウンタ=7、SERIAL 列値=7)。

IDENTITY 列と RowID 列

RowID 値の割り当てに対する INSERT OR UPDATE の影響は、IDENTITY 列があるかどうかによって異なります。

• IDENTITY 列がテーブルに定義されていない場合、挿入操作によって、連続する次の整数値が ID (RowID) 列に自動的に割り当てられます。更新操作は後続の挿入に影響しません。したがって、INSERT OR UPDATE は INSERT と同じ挿入操作を実行します。

• IDENTITY 列がテーブルに定義されている場合、INSERT OR UPDATE 操作が挿入になるか更新になるかを判断する前に、IDENTITY 列に整数を提供するために使用される内部カウンタが InterSystems IRIS で 1 つインクリメントされます。挿入操作では、インクリメントされたそのカウンタ値が IDENTITY 列に割り当てられます。ただし、INSERT OR UPDATE 操作が更新である必要があると InterSystems IRIS が判断した場合には、内部カウンタが既にインクリメントされていても、インクリメントされたそれらの整数値は割り当てられません。このため、次の INSERT OR UPDATE 操作が挿入である場合は、IDENTITY 列の整数シーケンスにギャップが生じます。RowID 列の値は IDENTITY 列の値から取得されるため、ID (RowID) の整数値の割り当てにギャップが生じます。

IDKEY 列値

INSERT OR UPDATE を使用している場合、IDKEY 列値は更新できず、挿入のみ可能です。テーブルに IDKEY インデックスと、別の UNIQUE 制約がある場合、INSERT OR UPDATE はこれらの列を組み合わせて、挿入または更新のどちらを実行するかを決定します。一方のキーの制約が失敗すると、INSERT OR UPDATE は挿入ではなく更新が強制的に実行されます。ただし、指定された IDKEY 列の値が既存の IDKEY 列の値と一致しない場合、この更新により IDKEY の列の変更が試行されるので、更新は失敗し、SQLCODE -107 エラーが生成されます。

列 A、B、C、および D があるテーブルについて考えます。主キーが IDKEY である環境に主キー (A,B) があり、UNIQUE 制約が列 (C,D) に適用されています。

SET OPTION PKEY_IS_IDKEY = TRUE

CREATE TABLE ABCD (
A INTEGER,
B INTEGER,
C INTEGER,
D INTEGER,
CONSTRAINT AB PRIMARY KEY (A,B),
CONSTRAINT CD UNIQUE (C,D))

テーブルには次の 2 つのデータ行もあります。

INSERT INTO ABCD SET A=1, B=1, C=2, D=2

INSERT INTO ABCD SET A=1, B=2, C=3, D=4

以下の値の挿入を試みるとします。

INSERT OR UPDATE ABCD (A,B,C,D) VALUES (2,2,3,4)

UNIQUE (C,D) 制約は失敗するため、この文は挿入を実行できません。代わりに、行 2 の更新を試みます。行 2 の IDKEY は (1,2) であるため、INSERT OR UPDATE 文は、列 A の値を 1 から 2 に変更しようとします。IDKEY の値を変更することはできないため、更新は SQLCODE -107 エラーで失敗します。

環境を既定の設定 (主キーは IDKEY ではない) にリセットします。

SET OPTION PKEY_IS_IDKEY = FALSE