SQLコマンドの使用によるデータの更新
UPDATE文を使用して、表の行を更新できます。同じシャード・キーを共有する表内の複数の行の更新がサポートされるようになりました。シャード・キーを構成する表の列については、ここを参照してください。
- Update文では、更新句を使用して、表内の1つ以上のフィールドの値を変更します。Oracle NoSQL Databaseでは、次の更新句がサポートされています:
- SET句では、1つ以上の既存フィールドの値が更新されます。
- ADD句では、1つ以上の配列に新しい要素が追加されます。
- PUT句では、1つ以上のマップに新しいフィールドが追加されます。既存のマップ・フィールドの値を更新することもできます。
- REMOVE句では、1つ以上の配列/マップから要素/フィールドを削除します。
- JSON MERGE句では、JSONドキュメントに対して一連の変更を行うことができます。
- SET TTL句では、行の有効期限が更新されます。
WHERE句では、どの行を更新するかを指定します。現在の実装では単一行の更新と複数行の更新の両方がサポートされるため、単一行の更新の場合はWHERE句で完全な主キーを指定し、複数行の更新の場合はシャード・キーを指定し、すべての行が同じシャードに存在するようにする必要があります。これにより、Oracle NoSQL DatabaseはACIDトランザクションでこの更新を実行できます。- オプションの
RETURNING句は、SELECT句と同じように機能します。つまり、"*"を指定でき、その場合は更新された完全な行が返されます。または、返される必要があるものを指定する式のリストを指定できます。RETURNING句は、WHERE句に完全な主キーが含まれている場合にのみ機能します。ノート:
現在、複数行の更新はサポートされていません。 - さらに、
WHERE条件を満たす行がない場合、UPDATE文は空の結果を返します。 - また、1つの更新問合せで更新できるレコードの数にも制限があります。デフォルトでは、1つの更新問合せで最大1,000レコードを変更できます。これをオーバーライドするには、
setLimit(int limit)メソッドを使用しますが、タイムアウトや待機時間の増加の原因となる可能性があるため、高く設定しすぎないようにしてください。詳細は、QueryRequest.setLimit(int limit)を参照してください。
例1: 列値を変更する単純な例。
UPDATE BaggageInfo
SET contactPhone = "823-384-1964",
confNo = "LE6J4Y"
WHERE ticketNo = 1762344493810説明: 前述の問合せは、SET句を使用して、特定のチケット番号のいくつかの列値を更新しています。
例 2: 行データを更新し、RETURNING句を使用して値をフェッチします。
UPDATE BaggageInfo
SET contactPhone = "823-384-1964",
confNo = "LE6J4Y"
WHERE ticketNo = 1762344493810 RETURNING *説明: 前述の問合せでは、RETURNING句を使用して、UPDATEトランザクションの完了後にデータをフェッチしています。
{"ticketNo":1762344493810,"fullName":"Adam Phillips","gender":"M","contactPhone":"823-384-1964",
"confNo":"LE6J4Y",
"bagInfo":{"bagInfo":[{"bagArrivalDate":"2019.02.02 at 03:13:00 AEDT","flightLegs":
[{"actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019.02.01 at 01:13:00 EST"},
{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019.02.01 at 00:47:00 EST"},
{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019.01.31 at 23:38:00 EST"}],
"estimatedArrival":"2019.02.01 at 03:00:00 PST","flightDate":"2019.02.01 at 01:00:00 EST",
"flightNo":"BM604","fltRouteDest":"LAX","fltRouteSrc":"MIA"},{"actions":
[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019.02.02 at 03:15:00 AEDT"},
{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019.02.01 at 07:35:00 PST"},
{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019.02.01 at 07:18:00 PST"}],
"estimatedArrival":"2019.02.02 at 03:15:00 AEDT","flightDate":"2019.01.31 at 22:13:00 PST",
"flightNo":"BM667","fltRouteDest":"MEL","fltRouteSrc":"LAX"}],"id":"79039899165297",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL",
"lastSeenTimeGmt":"2019.02.02 at 03:13:00 AEDT","routing":"MIA/LAX/MEL","tagNum":"17657806255240"}]}}例3: stream_acct表内の顧客のアカウント有効期限を更新します。
UPDATE stream_acct SET account_expiry="2023-12-28T00:00:00.0Z" WHERE acct_Id=3説明: 前述の問合せでは、SET句を使用して、ストリーミング・メディア・アプリケーションの特定のアカウントのaccount_expiryフィールドを更新しています。
複数行の更新
EmployeeInfoという名前の表を作成し、IntegerのempID列(主キー)、Stringのdepartment列(シャード・キー)、StringのfullName列およびJSON型のinfo列を設定します。
CREATE TABLE EmployeeInfo (
empID INTEGER,
department STRING,
fullName STRING,
info JSON,
PRIMARY KEY(SHARD(department), empID))その後、次の行を追加します:
INSERT INTO EmployeeInfo VALUES (101, "HR", "Liam Phillips", {"salary":100000,"address":{"city":"Toronto","country":"Canada"}})
INSERT INTO EmployeeInfo VALUES (102, "HR", "Emma Johnson", {"salary":105000,"address":{"city":"Melbourne","country":"Australia"}})
INSERT INTO EmployeeInfo VALUES (103, "IT", "Carlos Martinez", {"salary":110000,"address":{"city":"Barcelona","country":"Spain"}})
INSERT INTO EmployeeInfo VALUES (104, "Finance", "Sophia Becker", {"salary":130000,"address":{"city":"Munich","country":"Germany"}})例1: UPDATE文を使用して複数の行を更新します。
UPDATE EmployeeInfo emp
SET emp.info.address.city="Oslo",
SET emp.info.address.country="Norway",
SET emp.info.salary = emp.info.salary + 5000
where department="HR"説明: 前述の問合せでは、SET句によって、info.address.cityフィールドが「Oslo」、info.address.countryフィールドが「Norway」に更新され、シャード・キーとして指定されているdepartment列が「HR」と等しいすべての行のinfo.salaryフィールドが5000ずつ増分されます。このUPDATE文にはシャード・キーのみが記述されているため、データベースは更新された行数のみを返します。
+----------------+
| NumRowsUpdated |
+----------------+
| 2 |
+----------------+
1 row returnedここで、SELECT問合せを実行して、更新された行を確認します。info.address.cityおよびinfo.address.countryフィールドがそれぞれ「Oslo」および「Norway」に更新されたことを確認し、HR部門で働くすべての従業員のinfo.salaryフィールドが5000ずつ増分されたことを確認します。
select * from EmployeeInfo+-------+------------+-----------------+----------------------------+
| empID | department | fullName | info |
+-------+------------+-----------------+----------------------------+
| 103 | IT | Carlos Martinez | address |
| | | | city | Barcelona |
| | | | country | Spain |
| | | | salary | 110000 |
+-------+------------+-----------------+----------------------------+
| 101 | HR | Liam Phillips | address |
| | | | city | Oslo |
| | | | country | Norway |
| | | | salary | 105000 |
+-------+------------+-----------------+----------------------------+
| 102 | HR | Emma Johnson | address |
| | | | city | Oslo |
| | | | country | Norway |
| | | | salary | 110000 |
+-------+------------+-----------------+----------------------------+
| 104 | Finance | Sophia Becker | address |
| | | | city | Munich |
| | | | country | Germany |
| | | | salary | 130000 |
+-------+------------+-----------------+----------------------------+
4 rows returned例2: REURNING句を指定してUPDATE文を使用します
例2a: RETURNING句を使用して単一行を更新するには
UPDATE EmployeeInfo emp SET emp.info.salary = emp.info.salary + 1000 WHERE empID=101 and department="HR" RETURNING *説明: 前述の問合せでは、SET句によって、HR部門の従業員IDが101の従業員の給与を1000増やし、その後にRETURNING句が続いています。WHERE句で完全な主キーが指定されているため、更新は1行に影響し、RETURNING句が存在するので出力が直接返されます。
+-------+------------+---------------+----------------------+
| empID | department | fullName | info |
+-------+------------+---------------+----------------------+
| 101 | HR | Liam Phillips | address |
| | | | city | Oslo |
| | | | country | Norway |
| | | | salary | 106000 |
+-------+------------+---------------+----------------------+
1 row returned例2b: RETURNING句を使用して複数の行を更新するには
UPDATE EmployeeInfo emp SET emp.info.salary = emp.info.salary + 1000 WHERE department="HR" RETURNING *説明: 前述の問合せでは、SET句でHR部門の従業員の給与を1000ずつ増やすように指定し、その後にRETURNING句が続いています。HR部門には複数の従業員が在籍し、RETURNING句は複数行の更新では現在サポートされておらず、WHERE句で完全な主キーを指定する必要があるため、問合せによってエラーがスローされます。
Error handling command UPDATE EmployeeInfo emp SET emp.info.salary = emp.info.salary + 1000 WHERE department="HR" RETURNING *: Error: RETURNING clause is not supported unless the complete primary key is specified in the WHERE clause.JSONデータの更新
例1:いくつかのフィールドを更新し、ネストされたJSONドキュメントを挿入して、bagInfo JSON配列から既存のドキュメントを削除します。
UPDATE BaggageInfo b
JSON MERGE b.bagInfo[0].flightLegs[size(b.bagInfo[0].flightLegs)-1] WITH PATCH {"flightNo" : "BM107", "actions" : NULL, "tempActions" : {
"actionAt" : "LAX",
"actionStatus" : "in transit"
}},
WHERE ticketNo = 1762344493810 RETURNING *説明: 前述の問合せは、航空会社アプリケーションの乗客の手荷物追跡情報を更新しています。bagInfoフィールドは、乗客の手荷物追跡情報を格納するJSON配列です。JSON MERGEパッチを使用して、乗客の旅程の最後の区間の値を更新します。flightLegs JSON配列の最後の要素を計算するために、組込みのsize関数を使用しています。この関数は、移動セグメントの数を返し、それから1を減算しています。パッチ式で、更新するフィールドを指定します。ここでは、flightNo値を変更し、NULL値を指定してactionsフィールドを削除し、新しいJSONドキュメントtempActionsをflightLegs JSON配列に挿入しています。
{
"ticketNo" : 1762344493810,
"fullName" : "Adam Phillips",
"gender" : "M",
"contactPhone" : "893-324-1064",
"confNo" : "LE6J4Z",
"bagInfo" : [{
"bagArrivalDate" : "2019-02-01T16:13:00Z",
"flightLegs" : [{
"actions" : [{ ... }],
"estimatedArrival" : "2019-02-01T11:00:00Z",
"flightDate" : "2019-02-01T06:00:00Z",
"flightNo" : "BM604",
"fltRouteDest" : "LAX",
"fltRouteSrc" : "MIA"
}, {
"estimatedArrival" : "2019-02-01T16:15:00Z",
"flightDate" : "2019-02-01T06:13:00Z",
"flightNo" : "BM107",
"fltRouteDest" : "MEL",
"fltRouteSrc" : "LAX",
"tempActions" : {
"actionAt" : "LAX",
"actionStatus" : "in transit"
}
}],
"id" : "79039899165297",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MEL",
"lastSeenTimeGmt" : "2019-02-01T16:13:00Z",
"routing" : "MIA/LAX/MEL",
"tagNum" : "17657806255240"
}]
}ノート:
ここで、bagInfoフィールドはJSONドキュメントの配列で、乗客ごとの受託手荷物を表します。更新する配列要素を指定しない場合、JSON MERGE句によって、bagInfo JSON配列全体がパッチの内容に置き換えられます。1人の乗客に複数の受託手荷物がある場合は、同じUPDATE文でJSON MERGE句を繰り返し使用できます。
例2: 更新文でJSON MERGEパッチおよびPUT句を使用します。
UPDATE BaggageInfo b
JSON MERGE b.bagInfo[0].flightLegs WITH PATCH {"flightNo" : "BM107", "actions" : NULL, "tempActions" : {
"actionAt" : "LAX",
"actionStatus" : "in transit"
}},
JSON MERGE b.bagInfo[1].flightLegs WITH PATCH {"flightNo" : "BM107", "actions" : NULL, "tempActions" : {
"actionAt" : "LAX",
"actionStatus" : "in transit"
}},
PUT b.bagInfo[0]{"bagArrivalDate" : "2019-03-13T00:00:00Z", "lastSeenStation" : "SFO", "routing" : "SFO/LAX",},
PUT b.bagInfo[1]{"bagArrivalDate" : "2019-03-13T00:00:00Z", "lastSeenStation" : "SFO", "routing" : "SFO/LAX",}
WHERE ticketNo = 1762320369957 RETURNING *説明: 前述の問合せでは、乗客のレコードに2つの受託手荷物が含まれています。JSON MERGE句を繰り返し使用して、bagInfo JSON配列の両方の要素の手荷物追跡情報を更新しています。ターゲットのパス式には、flightLegs配列の個々の要素ではなく、flightLegsオブジェクトが含まれています。このため、パッチは、flightLegs JSON配列全体をflightLegs JSONオブジェクトに置き換えます。
PUT句は、bagInfo JSON配列の指定された要素内のbagArrivalDate、lastSeenStationおよびroutingフィールドの値を更新しています。
{
"ticketNo" : 1762320369957,
"fullName" : "Lorenzo Phil",
"gender" : "M",
"contactPhone" : "364-610-4444",
"confNo" : "QI3V6Z",
"bagInfo" : [{
"bagArrivalDate" : "2019-03-13T00:00:00Z",
"flightLegs" : {
"flightNo" : "BM107",
"tempActions" : {
"actionAt" : "LAX",
"actionStatus" : "in transit"
}
},
"id" : "79039899187755",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "SFO",
"lastSeenTimeGmt" : "2019-03-12T15:05:00Z",
"routing" : "SFO/LAX",
"tagNum" : "17657806240001"
}, {
"bagArrivalDate" : "2019-03-13T00:00:00Z",
"flightLegs" : {
"flightNo" : "BM107",
"tempActions" : {
"actionAt" : "LAX",
"actionStatus" : "in transit"
}
},
"id" : "79039899197755",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "SFO",
"lastSeenTimeGmt" : "2019-03-12T15:05:00Z",
"routing" : "SFO/LAX",
"tagNum" : "17657806340001"
}]
}
JSONコレクション表の行の更新
ショッピング・アプリケーション表用に作成されたJSONコレクション表の行について考えてみます。
例:storeAcct表内の誤った買物客のデータ・レコードを変更します。
UPDATE文を使用して、JSONコレクション表の既存のドキュメントのフィールドを更新できます。UPDATE操作は、固定スキーマ表と同様に機能します。
{"contactPhone":"1617114988","address":{"Dropbox":"Presidency College","city":"Kansas City","state":"Alabama","zip":95065},"cart":[{"item":"A4 sheets","priceperunit":500,"quantity":2},{"item":"Mobile Holder","priceperunit":700,"quantity":1}],"email":"lorphil@usmail.com","firstName":"Lorenzo","lastName":"Phil","notify":"yes","orders":[{"EstDelivery":"2023-11-15","item":"AG Novels 1","orderID":"101200,"priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wall paper","orderID":"101200,"priceperunit":950,"status":"Transit"}]}UPDATE storeAcct s
SET s.notify = "no",
REMOVE s.cart [$element.item = "A4 sheets"],
PUT s.address {"Block" : "C"},
SET s.orders[0].EstDelivery = "2023-11-17",
ADD s.cart 1 {"item":"A3 sheets", "priceperunit":600, "quantity":2}
WHERE s.contactPhone = "1617114988"説明:前述の例では、storeAcctテーブル内の買物客のレコードを更新して、不注意なエラーをいくつか修正しています。この修正には、storeAcct表の様々なフィールドの更新が必要です。SET句は、買物客のデータ・レコードの通知設定を非アクティブ化します。REMOVE句は、カート内のいずれかのitemフィールドがA4 sheetsと一致するかどうかをチェックし、対応する要素をorders配列から削除します。PUT句は、配信のランドマークを示す新しいJSONフィールドを追加します。2番目のSET句は、深くネストされたEstDeliveryフィールドにアクセスし、orders配列の最初の商品の推定配信日を更新します。ADD句は、cartフィールドに新しい要素を挿入して、追加商品の候補リストを作成します。
更新された買物客のデータをフェッチすると、次の出力が表示されます。
{"contactPhone":"1617114988","address":{"Block":"C","Dropbox":"Presidency College","city":"Kansas City","state":"Alabama","zip":95065},"cart":[{"item":"Mobile Holder","priceperunit":700,"quantity":1},{"item":"A3 sheets","priceperunit":600,"quantity":2}],"email":"lorphil@usmail.com","firstName":"Lorenzo","lastName":"Phil","notify":"no","orders":[{"EstDelivery":"2023-11-17","item":"AG Novels 1","orderID":"101200,"priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wall paper","orderID":"101200,"priceperunit":950,"status":"Transit"}]}