SQLコマンドの使用によるデータのアップサート
行が存在しない場合は挿入し、存在する場合はその行を新しい値で更新する場合に、UPSERT文を使用します。
例: UPSERTコマンドを使用してBaggageInfo表内のデータを更新します。
フルネームが
Adam Phillipsの顧客に関する既存の詳細を次に示します。SELECT * FROM BaggageInfo WHERE fullname="Adam Phillips"
{
"ticketNo" : 1762344493810,
"fullName" : "Adam Phillips",
"gender" : "M",
"contactPhone" : "893-324-1064",
"confNo" : "LE6J4Z",
"bagInfo" : [{
"bagArrivalDate" : "2019-02-01T16:13:00Z",
"flightLegs" : [{
"actions" : [{
"actionAt" : "MIA",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-02-01T06:13:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "BagTag Scan at MIA",
"actionTime" : "2019-02-01T05:47:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "Checkin at MIA",
"actionTime" : "2019-02-01T04:38:00Z"
}],
"estimatedArrival" : "2019-02-01T11:00:00Z",
"flightDate" : "2019-02-01T06:00:00Z",
"flightNo" : "BM604",
"fltRouteDest" : "LAX",
"fltRouteSrc" : "MIA"
}, {
"actions" : [{
"actionAt" : "MEL",
"actionCode" : "Offload to Carousel at MEL",
"actionTime" : "2019-02-01T16:15:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MEL",
"actionTime" : "2019-02-01T15:35:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-02-01T15:18:00Z"
}],
"estimatedArrival" : "2019-02-01T16:15:00Z",
"flightDate" : "2019-02-01T06:13:00Z",
"flightNo" : "BM667",
"fltRouteDest" : "MEL",
"fltRouteSrc" : "LAX"
}],
"id" : "79039899165297",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MEL",
"lastSeenTimeGmt" : "2019-02-01T16:13:00Z",
"routing" : "MIA/LAX/MEL",
"tagNum" : "17657806255240"
}]
}
1 row returned既存の行は、
UPSERTコマンドを使用して変更します。オプションのRETURNING句を使用して、UPSERTの実行後に値をフェッチできます。フルネームがAdam Phillipsの顧客の更新後の値は、次に示すようにフェッチされます。UPSERT INTO BaggageInfo VALUES(
1762344493810,
"Adam Phillips",
"M",
"893-324-1864",
"LE6J4Y",
[ {
"id" : "79039899165297",
"tagNum" : "17657806255240",
"routing" : "MIA/LAX/MEL",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MEL",
"flightLegs" : [ {
"flightNo" : "BM604",
"flightDate" : "2019-02-01T06:00:00Z",
"fltRouteSrc" : "MIA",
"fltRouteDest" : "LAX",
"estimatedArrival" : "2019-02-01T11:00:00Z",
"actions" : [ {
"actionAt" : "MIA",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-02-01T06:13:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "BagTag Scan at MIA",
"actionTime" : "2019-02-01T05:47:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "Checkin at MIA",
"actionTime" : "2019-02-01T04:38:00Z"
} ]
}, {
"flightNo" : "BM667",
"flightDate" : "2019-02-01T06:13:00Z",
"fltRouteSrc" : "LAX",
"fltRouteDest" : "MEL",
"estimatedArrival" : "2019-02-01T16:15:00Z",
"actions" : [ {
"actionAt" : "MEL",
"actionCode" : "Offload to Carousel at MEL",
"actionTime" : "2019-02-01T16:15:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MEL",
"actionTime" : "2019-02-01T15:35:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-02-01T15:18:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-02-01T16:18:00Z",
"bagArrivalDate" : "2019-02-01T16:18:00Z"
} ]
) RETURNING *
{
"ticketNo" : 1762344493810,
"fullName" : "Adam Phillips",
"gender" : "M",
"contactPhone" : "893-324-1864",
"confNo" : "LE6J4Y",
"bagInfo" : [{
"bagArrivalDate" : "2019-02-01T16:18:00Z",
"flightLegs" : [{
"actions" : [{
"actionAt" : "MIA",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-02-01T06:13:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "BagTag Scan at MIA",
"actionTime" : "2019-02-01T05:47:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "Checkin at MIA",
"actionTime" : "2019-02-01T04:38:00Z"
}],
"estimatedArrival" : "2019-02-01T11:00:00Z",
"flightDate" : "2019-02-01T06:00:00Z",
"flightNo" : "BM604",
"fltRouteDest" : "LAX",
"fltRouteSrc" : "MIA"
}, {
"actions" : [{
"actionAt" : "MEL",
"actionCode" : "Offload to Carousel at MEL",
"actionTime" : "2019-02-01T16:15:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MEL",
"actionTime" : "2019-02-01T15:35:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-02-01T15:18:00Z"
}],
"estimatedArrival" : "2019-02-01T16:15:00Z",
"flightDate" : "2019-02-01T06:13:00Z",
"flightNo" : "BM667",
"fltRouteDest" : "MEL",
"fltRouteSrc" : "LAX"
}],
"id" : "79039899165297",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MEL",
"lastSeenTimeGmt" : "2019-02-01T16:18:00Z",
"routing" : "MIA/LAX/MEL",
"tagNum" : "17657806255240"
}]
}ノート:
UPSERT文のすべての列に値を指定しない場合、対応するCREATE TABLE文にそのようなオプションが指定されている場合、またはそれらの列にNULL値が割り当てられている場合は、それらの列にDEFAULT値が取得されます。例: UPSERTコマンドを使用してBaggageInfo表にデータを挿入します。
UPSERTコマンドを使用して、フルネームがBirgit Naquinの顧客の新しいエントリ値を追加します。SELECT * FROM BaggageInfo WHERE fullname="Birgit Naquin";
0 row returned
UPSERT INTO BaggageInfo VALUES(
1762392196147,
"Birgit Naquin",
"M",
"165-742-5715",
"QD1L0T",
[ {
"id" : "7903989918469",
"tagNum" : "17657806240229",
"routing" : "JFK/MAD",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MAD",
"flightLegs" : [ {
"flightNo" : "BM495",
"flightDate" : "2019-03-07T07:00:00Z",
"fltRouteSrc" : "JFK",
"fltRouteDest" : "MAD",
"estimatedArrival" : "2019-03-07T14:00:00Z",
"actions" : [ {
"actionAt" : "MAD",
"actionCode" : "Offload to Carousel at MAD",
"actionTime" : "2019-03-07T13:54:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "ONLOAD to MAD",
"actionTime" : "2019-03-07T07:00:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "BagTag Scan at JFK",
"actionTime" : "2019-03-07T06:53:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "Checkin at JFK",
"actionTime" : "2019-03-07T05:03:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-03-07T13:51:00Z",
"bagArrivalDate" : "2019-03-07T13:51:00Z"
} ]
)
{"NumRowsInserted":1}
1 row returned結果には、新しい行が挿入されたことを示す
{"NumRowsInserted":1}が表示されます。UPSERTコマンドを使用して挿入された値は、次のように表示できます:SELECT * FROM BaggageInfo where fullname="Birgit Naquin"
{
"ticketNo" : 1762392196147,
"fullName" : "Birgit Naquin",
"gender" : "M",
"contactPhone" : "165-742-5715",
"confNo" : "QD1L0T",
"bagInfo" : [{
"bagArrivalDate" : "2019-03-07T13:51:00Z",
"flightLegs" : [{
"actions" : [{
"actionAt" : "MAD",
"actionCode" : "Offload to Carousel at MAD",
"actionTime" : "2019-03-07T13:54:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "ONLOAD to MAD",
"actionTime" : "2019-03-07T07:00:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "BagTag Scan at JFK",
"actionTime" : "2019-03-07T06:53:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "Checkin at JFK",
"actionTime" : "2019-03-07T05:03:00Z"
}],
"estimatedArrival" : "2019-03-07T14:00:00Z",
"flightDate" : "2019-03-07T07:00:00Z",
"flightNo" : "BM495",
"fltRouteDest" : "MAD",
"fltRouteSrc" : "JFK"
}],
"id" : "7903989918469",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MAD",
"lastSeenTimeGmt" : "2019-03-07T13:51:00Z",
"routing" : "JFK/MAD",
"tagNum" : "17657806240229"
}]
}
1 row returnedノート:
UPSERT文のすべての列に値を指定しない場合、対応するCREATE TABLE文にそのようなオプションが指定されている場合、またはそれらの列にNULL値が割り当てられている場合は、それらの列にDEFAULT値が取得されます。UPSERTコマンドの一部としてオプションのRETURNING句を使用することもできます。例: UPSERT文を使用して
stream_acct表内のデータを追加または変更します。UPSERT INTO stream_acct VALUES
(
1,
"AP",
"2023-10-18",
{
"firstName": "Adam",
"lastName": "Phillips",
"country": "Germany",
"contentStreamed": [{
"showName": "At the Ranch",
"showId": 26,
"showtype": "tvseries",
"genres": ["action", "crime", "spanish"],
"numSeasons": 4,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Season 1 episode 1",
"lengthMin": 75,
"minWatched": 75,
"date": "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName": "Season 1 episode 2",
"minWatched": 40,
"date": "2022 - 04 - 18 "
}]
},
{
"seasonNum": 2,
"numEpisodes": 2,
"episodes": [{
"episodeID": 40,
"episodeName": "Season 2 episode 1",
"lengthMin": 40,
"minWatched": 30,
"date": "2022-04-25"
},
{
"episodeID": 50,
"episodeName": "Season 2 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022-04-27"
}]
},
{
"seasonNum": 3,
"numEpisodes": 2,
"episodes": [{
"episodeID": 60,
"episodeName": "Season 3 episode 1",
"lengthMin": 20,
"minWatched": 20,
"date": "2022-04-25"
},
{
"episodeID": 70,
"episodeName": "Season 3 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022 - 04 - 27 "
}]
}]
},
{
"showName": "Bienvenu",
"showId": 15,
"showtype": "tvseries",
"genres": ["comedy", "french"],
"numSeasons": 2,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Bonjour",
"lengthMin": 45,
"minWatched": 45,
"date": "2022-03-07"
},
{
"episodeID": 30,
"episodeName": "Merci",
"lengthMin": 42,
"minWatched": 42,
"date": "2022-03-08"
}]
}]
}]
}
) RETURNING * 前述の例では、stream_acct表にacct_id =1に対応する行がない場合、新しい行が挿入されます。それ以外の場合は、acct_id =1の値を持つ既存の行が更新されます。
例: 新しい買物客のレコードをstoreAcct表に追加します。
UPSERT文を使用すると、新しいドキュメントを追加したり、JSONコレクション表の既存のドキュメント内のフィールドを更新できます。ショッピング・アプリケーション表用に作成されたJSONコレクション表について考えてみます。
UPSERT into storeAcct values ("1417114588", {"firstName" : "Dori", "lastName" : "Martin", "email" : "dormartin@usmail.com", "address" : {"Dropbox" : "Presidency College"}}) RETURNING *;前述の例では、UPSERT文を使用して、storeAcct表に新しい行を追加します。
UPSERT文を使用して、買物客の情報を更新できます。UPSERT文に指定されたフィールドのみがドキュメントで更新されます。省略されたフィールドはドキュメントから削除されます。
出力:
{"contactPhone":"1417114588","address":{"Dropbox":"Presidency College"},"email":"lorphil@usmail.com","firstName":"Dori","lastName":"Martin"}