Upsert文

UPSERTという語はUPDATEとINSERTと組み合せたもので、この文の機能を表しています。

構文:

upsert_statement ::= 
   [variable_declaration] 
   UPSERT INTO table_name 
   [[AS] table_alias]
   ["(" id ("," id)* ")"] 
   VALUES "(" insert_clause ("," insert_clause)* ")"
   [SET TTL ttl_clause ] 
   [returning_clause]

insert_clause ::= DEFAULT | expression

returning_clause ::= RETURNING select_list

行が存在しない場合は挿入し、存在する場合はその行を新しい値で更新する場合に、UPSERT文を使用します。

• 表名の後にオプションの列を指定できます。このリストには、表の列のサブセットの列名が含まれます。このサブセットには、すべての主キー列が含まれている必要があります。列リストが存在しない場合、デフォルトの列リストは、表のすべての列がCREATE TABLE文で指定された順序で含まれる列リストです。
• 列リストの列は、VALUES句の後にリストされた式(またはDEFAULTキーワード)に1対1で対応します(式/DEFAULTの数が列数と一致しない場合はエラーが発生します)。
• VALUESリストの後にSET TTL句を使用すると、アップサート(挿入/更新)行の有効期限を設定できます。
• RETURNING句がない場合、UPSERT文の結果は、NumRowsInsertedという名前の単一のフィールドを持つレコードで、その値は挿入された行数です。行が存在し、更新が発生した場合は0、それ以外の場合は1です。RETURNING句がある場合、SELECT句と同じように機能します。その場合、*を指定することもでき、行全体が返されます。または、返す必要がある内容を指定する式のリストを含めることができます。

users表には、次に示すように3つの行があります:

SELECT count(*) FROM users
{"Column_1":3}

例7-11 UPSERTコマンドを使用したusers表のデータの更新

IDが10のユーザーの既存の値を次に示します。

SELECT * FROM users WHERE id=10

{
  "id" : 10,
  "firstName" : "John",
  "lastName" : "Smith",
  "otherNames" : [{
    "first" : "Johny",
    "last" : "BeGood"
  }],
  "age" : 22,
  "income" : 45000,
  "address" : {
    "city" : "Reno",
    "number" : 10,
    "state" : "NV",
    "street" : "Main"
  },
  "connections" : [30, 55, 43],
  "expenses" : null
}

既存の行は、UPSERTコマンドを使用して変更します。otherNames配列およびincomeフィールドが変更されます。

 UPSERT INTO users VALUES (
     10,
     "John",
     "Smith",
     [ {"first" : "Johny", "last" : "AlwaysGood"} ],
     22,
     80000,
     { "street" : "Main", "number" : 10, "city" : "Reno", "state" : "NV"},
     [ 30, 55, 43 ],
    DEFAULT
 )

{"NumRowsInserted":0}

1 row returned

結果は、行が更新されたことを示す{"NumRowsInserted":0}として取得されます。ID 10のユーザーの更新後の値は、次に示すようにSELECT文で検証できます。

SELECT * FROM users WHERE id=10

{
  "id" : 10,
  "firstName" : "John",
  "lastName" : "Smith",
  "otherNames" : [{
    "first" : "Johny",
    "last" : "AlwaysGood"
  }],
  "age" : 22,
  "income" : 80000,
  "address" : {
    "city" : "Reno",
    "number" : 10,
    "state" : "NV",
    "street" : "Main"
  },
  "connections" : [30, 55, 43],
  "expenses" : null
}

例7-12 UPSERT文を使用した一部の列のみの更新

UPSERT文を使用して、VALUES句で少数の列のみの値を指定し、INTO句で対応する列名を指定しない場合、次に示すように表の列の数がVALUES句の値の数と一致しないことを示すエラーが表示されます。

UPSERT INTO users VALUES ( 11,  "John", "Smith" )

Error handling command UPSERT INTO users VALUES ( 11,  "John", "Smith" ):
Error: at (1, 0) The number of VALUES expressions is not equal to the number of table columns

このエラーを回避するには、表名の後に列リストを指定します。ここで、UPSERT文のすべての列に値を指定しない場合、対応するCREATE TABLE文でそのようなオプションが指定されている場合、または次に示すようにそれらの列にNULL値が割り当てられている場合は、これらの列にDEFAULT値が取得されます。

UPSERT INTO users(id,firstName,lastName) VALUES (11,"John","Smith")

{"NumRowsInserted":1}
1 row returned

結果は、新しい行が1つ挿入されたことを意味する{"NumRowsInserted":1}として取得されます。ID 11のユーザーの更新後の値は、次に示すようにSELECT文で検証できます。

SELECT * FROM users WHERE id=11

{
  "id" : 11,
  "firstName" : "John",
  "lastName" : "Smith",
  "otherNames" : null,
  "age" : null,
  "income" : null,
  "address" : null,
  "connections" : null,
  "expenses" : null
}

UPSERT文の一部ではないすべてのフィールドにNULL値があります。

ノート:

列にNULL以外の値(前述の問合せではlastNamesなど)がある場合でも、次に示すように後続のUPSERT文で省略すると、NULLになる可能性があります。ここでは、オプションのRETURNING文を使用して、UPSERTの実行後にデータをフェッチします。

UPSERT INTO users(id,firstName) VALUES (11,"Joseph") returning *

{
  "id" : 11,
  "firstName" : "Joseph",
  "lastName" : null,
  "otherNames" : null,
  "age" : null,
  "income" : null,
  "address" : null,
  "connections" : null,
  "expenses" : null
}

列lastNamesは、UPSERT文のためNULLになりました。

例7-13 新しい買物客のレコードをstoreAcct表に追加します。

UPSERT文を使用して、新しいドキュメントを追加したり、JSONコレクション表の既存のドキュメント内のフィールドを更新したりすることができます。ショッピング・アプリケーション用に作成されたJSONコレクション表について考えてみます。

UPSERT into storeAcct values ("1417114588", {"firstName" : "Dori", "lastName" : "Martin", "email" : "[e-mail]", "address" : {"Dropbox" : "Presidency College"}}) RETURNING *;

説明:前述の例では、UPSERT文を使用して、storeAcct表に新しい行を追加しています。

UPSERT文を使用して、買物客の情報を更新できます。UPSERT文に指定されたフィールドのみがドキュメントで更新されます。省略されたフィールドはドキュメントから削除されます。

出力:

{"contactPhone":"1417114588","address":{"Dropbox":"Presidency College"},"email":"[e-mail]","firstName":"Dori","lastName":"Martin"}