INSERTおよびUPSERTを使用した表の行の追加

このトピックでは、SQL for Oracle NoSQL DatabaseのINSERT文およびUPSERT文のを使用して、表の行を追加する方法の例を示します。

INSERT文を使用して、既存の表にある1つの行を挿入または更新できます。

例:

SQLBasicExamplesスクリプトを実行した場合は、Usersという名前の表がすでに作成されています。この表には、次の定義があります。

CREATE TABLE Users 
(
	id integer,
	firstname string,
	lastname string,
	age integer,
	income integer,
	primary key (id)
); 
sql-> describe table Users;
 === Information ===
 +-------+-----+-------+----------+----------+--------+----------+---------+-------------+
 | name  | ttl | owner | sysTable | r2compat | parent | children | indexes | description |
 +-------+-----+-------+----------+----------+--------+----------+---------+-------------+
 | Users |     |       | N        | N        |        |          |         |             |
 +-------+-----+-------+----------+----------+--------+----------+---------+-------------+

 === Fields ===
 +----+-----------+---------+----------+-----------+----------+------------+----------+
 | id |   name    |  type   | nullable |  default  | shardKey | primaryKey | identity |
 +----+-----------+---------+----------+-----------+----------+------------+----------+
 |  1 | id        | Integer | N        | NullValue | Y        | Y          |          |
 +----+-----------+---------+----------+-----------+----------+------------+----------+
 |  2 | firstname | String  | Y        | NullValue |          |            |          |
 +----+-----------+---------+----------+-----------+----------+------------+----------+
 |  3 | lastname  | String  | Y        | NullValue |          |            |          |
 +----+-----------+---------+----------+-----------+----------+------------+----------+
 |  4 | age       | Integer | Y        | NullValue |          |            |          |
 +----+-----------+---------+----------+-----------+----------+------------+----------+
 |  5 | income    | Integer | Y        | NullValue |          |            |          |
 +----+-----------+---------+----------+-----------+----------+------------+----------+

新しい行をUsers表に挿入するには、次のようにINSERT文を使用します。すべての表の列に値を追加するため、列名を明示的に指定する必要はありません。

sql-> INSERT INTO Users VALUES (10, "John", "Smith", 22, 45000);
{"NumRowsInserted":1}
1 row returned
sql-> select * from Users;
{"id":10,"firstname":"John","lastname":"Smith","age":22,"income":45000}

すべてではなく一部の表の列にデータを挿入する場合は、INSERT文で列名を明示的に指定します。指定しない列には、NULLまたは表の作成時に指定したデフォルト値のいずれかが割り当てられます。

sql-> INSERT INTO Users (id, firstname, income)
VALUES (11, "Mary", 5000);
{"NumRowsInserted":1}
1 row returned

sql-> select * from Users;
{"id":11,"firstname":"Mary","lastname":null,"age":null,"income":5000}
{"id":10,"firstname":"John","lastname":"Smith","age":22,"income":45000}
2 rows returned

UPSERT文の使用方法

UPSERTという語はUPDATEとINSERTと組み合せたもので、この文の機能を表しています。行が存在しない場合は挿入し、存在する場合はその行を新しい値で更新する場合に、UPSERT文を使用します。

たとえば、前の項の説明に従ってすでに新しい行を挿入している場合は、次の文を実行すると、ユーザーJohnの年齢が27、収入が60,000に更新されます。前のINSERT文を実行していない場合、UPSERT文によって、ユーザーIDが10の新しい行がUsers表に挿入されます。

sql-> UPSERT INTO Users VALUES (10, "John", "Smith", 27, 60000);
{"NumRowsInserted":0}
1 row returned
sql-> UPSERT INTO Users VALUES (11, "Mary", "Brown", 28, 70000);
{"NumRowsInserted":0}
1 row returned

sql-> select * from Users;
{"id":10,"firstname":"John","lastname":"Smith","age":22,"income":60000}
{"id":11,"firstname":"Mary","lastname":"Brown","age":28,"income":70000}
2 rows returned

IDENTITY列の使用方法

IDENTITY列を使用すると、新しい表の行を挿入するたびに、表の列の値を自動的に生成できます。SQLリファレンス・ガイドのIDENTITY列を参照してください。

次のIDENTITY列のスタイル両方について、INSERT文を使用する方法の例を示します。

GENERATED ALWAYS AS IDENTITY
GENERATED BY DEFAULT [ON NULL] AS IDENTITY

1つの列DeptIdをGENERATED ALWAYS AS IDENTITYとして使用して、Employee_testという名前の表を作成します。このIDENTITY列は主キーではありません。表に数行を挿入します。

sql-> CREATE TABLE EmployeeTest
(
    Empl_id INTEGER,
    Name STRING,
    DeptId INTEGER GENERATED ALWAYS AS IDENTITY (CACHE 1),
    PRIMARY KEY(Empl_id)
);

INSERT INTO Employee_test VALUES (148, 'Sally', DEFAULT);
INSERT INTO Employee_test VALUES (250, 'Joe', DEFAULT);
INSERT INTO Employee_test VALUES (346, 'Dave', DEFAULT);

INSERT文によって次の行が挿入され、IDENTITY列DeptIdには1、2および3の値が生成されます。

Empl_id	Name	DeptId
148	Sally	1
250	Joe	2
346	Dave	3

Employee_test表に行を挿入するときに、DeptId IDENTITY列の値を指定することはできません。これは、この列をGENERATED ALWAYS AS IDENTITYとして定義したためです。列値にDEFAULTを指定すると、次のIDENTITY値が生成されます。反対に、次のSQL文を実行しようとすると、DeptId列に値(200)を指定しているため、例外が発生します。

sql-> INSERT INTO Employee_test VALUES (566, 'Jane', 200);

Employee_test表の列をGENERATED BY DEFAULT AS IDENTITYとして作成すると、値を指定しなかった場合にのみ、システムによって値が生成されます。たとえば、次のようにEmployee_test表を定義して、前述したようにINSERT文を実行すると、従業員のDeptId列に値200が挿入されます。

CREATE Table Employee_test
(
    Empl_id INTEGER,
    Name STRING,
    DeptId INTEGER GENERATED BY DEFAULT AS IDENTITY (CACHE 1),
    PRIMARY KEY(Empl_id)
);