UpsertData()

Updates data in a data extension if matching columns and values are found, and inserts rows if no matches are found. This function returns the number of rows updated or inserted.

You can use this function in landing pages, microsites, and SMS messages in MobileConnect. Use the UpsertDE() function to update or insert rows in a data extension in an email.

The UpsertData() function has six parameters:

  • dataExt (string): Required. The name of the data extension that you want to update or insert data into.
  • numCols (number): Required. The number of columns of data to update or insert.
  • searchColumnName (string): Required. The name of the column to search for the data you want to update or insert.
  • searchValue (string): Required. The value that the function uses to determine which row to update or insert.
  • columnToUpsert (string): Required. The column to update or insert data into.
  • upsertedValue (string): Required. The value to update or insert into the specified column.

You can also use this function to upsert multiple columns in a single row. See Usage for more information.

To use the function, first pass it the name of the data extension that contains the data you want to update or insert. Next, pass it the name of the column to search, and the value that identifies the row to update or insert. Finally, pass it the name of the column that you want to update or insert the data into, and the value of that column.

You can use the UpsertData() function to upsert more than one column in a single row in one operation. To upsert multiple columns, you must provide one set of search criteria for each column that you want to upsert.

This example uses a data extension called "Flights" that contains the data in this table.

FlightIdOriginDestPricePerBagSurcharge
1INDNYC100
2INDLAX200
3INDSEA50025

This function upserts data for FlightId 3 and FlightId 4.

Because FlightId 1 exists in the data extension, the function updates the values in the Dest, Price, and PerBagSurcharge columns. However, FlightId 4 doesn’t exist, so the function inserts a row that contains the specified data. The resulting data extension contains the data in this table.

FlightIdOriginDestPricePerBagSurcharge
1INDJFK7510
2INDLAX20025
3INDSEA50025
4JFKIND6010

The function returns the number of rows that were updated or inserted by each of the functions. In this case, each function updated or inserted one row.

If you receive errors or notice incomplete upsert operations when you use this function, consider the following factors:

  • If you specify a value for the numCols parameter that doesn’t agree with the number of upserts that you included in the function, the function produces an exception.
  • If you specify a column name for the searchColumnName parameter that doesn’t exist in the specified data extension, the function produces an exception.
  • If you specify a value for the upsertedValue parameter that is of a different data type than the column that you specified in the columnToUpsert parameter, the function doesn’t update or insert any data and returns 0. For example, if you pass a numeric value, but the column contains string data, the function doesn’t update or insert data.
  • If the number of search parameters (searchColumnName and searchValue) doesn’t equal the number of upsert parameters (columnToUpsert and upsertedValue), the function only updates the columns for which you have provided search parameters, or inserts a row with that number of columns populated. For example, if you provide two search parameters and three upsert parameters, the function only implements the first two upsert parameters. You can repeat the same upsert parameters multiple times if necessary.