UpsertDE()

Updates data in a data extension if matching columns and values are found, and inserts rows if no matches are found. This function doesn’t return any output.

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

The UpsertDE() function has six parameters:

  • dataExt (string): Required. The name of the data extension that you want to update or insert data into.
  • columnValuePairs (number): Required. The number of column and value pairs for the function to match against.
  • searchColumnName1 (string): Required. The name of the column to search for the data you want to update or insert.
  • searchValue1 (string): Required. The value that the function uses to determine which row to update or insert. You can specify multiple column and value pairs to match against.
  • columnToUpsert1 (string): Required. The column to update or insert data into.
  • upsertedValue1 (string): Required. The value to update or insert into the specified column. You can specify multiple column and value pairs to upsert.

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 UpsertDE() 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

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 columnValuePairs parameter that doesn’t agree with the number of column and value pairs to match against, the function returns 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.