Previous Next JavaScript must be enabled to correctly display this content

## 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 returned

You 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 returned

The 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 returned

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. 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"}