Using SQL command to upsert data
Use an UPSERT statement to insert a row where it does not exist, or to update the row with new values when it does.
Example : Updating data in the BaggageInfo table
using UPSERT command.
The existing details for the customer with full name
Adam
Phillips is shown
below.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 returnedYou modify the existing row using the
UPSERT command. You
can use an optional RETURNING clause to fetch the values after UPSERT is performed. The
updated value for the customer with full name Adam Phillips is fetched as shown
below.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"
}]
}Note:
If you do not supply values for all the columns in a UPSERT statement, then those columns get a DEFAULT value if such an option is specified in the corresponding CREATE TABLE statement or those columns are assigned NULL values.Example : Inserting data in the BaggageInfo table
using UPSERT command.
A new entry value for a customer with full name
Birgit
Naquin is added using the UPSERT
command.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 returnedThe result shows
{"NumRowsInserted":1} which implies a new
row has been inserted. The value inserted using the UPSERT command can
be viewed as shown
below: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 returnedNote:
If you do not supply values for all the columns in a UPSERT statement, then those columns get a DEFAULT value if such an option is specified in the corresponding CREATE TABLE statement or those columns are assigned NULL values. You can also use an optional RETURNING clause as part of the UPSERT command.Example : Use UPSERT statement to add/modify data in the
stream_acct
table.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 * In the above example, a new row is inserted if the stream_acct
table does not have a row corresponding to acct_id =1. Else the existing row
with the value of acct_id =1 is updated.
Example : Add a new shopper's record to the storeAcct
table.
You can use the UPSERT statement to add a new document or update fields in an existing document in the JSON collection tables. Consider the JSON collection table created for a shopping application table.
UPSERT into storeAcct values ("1417114588", {"firstName" : "Dori", "lastName" : "Martin", "email" : "dormartin@usmail.com", "address" : {"Dropbox" : "Presidency College"}}) RETURNING *;In the above example, you use the UPSERT statement to add a new row to the
storeAcct table.
You can use the UPSERT statement to update a shopper's information. Only the fields supplied in the UPSERT statement are updated in the document. The omitted fields are removed from the document.
Output:
{"contactPhone":"1417114588","address":{"Dropbox":"Presidency College"},"email":"lorphil@usmail.com","firstName":"Dori","lastName":"Martin"}