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.

1UpsertData(dataExt, columnValuePairs,
2         searchColumnName1, searchValue1,
3         [searchColumnName2, searchValue2,]
4         columnToUpsert1, upsertedValue1,
5         [columnToUpsert2, upsertedValue2])

The UpsertData() 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.
• 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. You can specify multiple column and value pairs to match against.
• 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 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 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.

This function upserts data for FlightId 3 and FlightId 4.

1%%[
2  Var @upsert1, @upsert2
3  Set @upsert1 = UpsertData("Flights", /* Data extension that contains the data to upsert */
4                            3, /* Number of column and value pairs for the function to match against */
5                            "FlightId", 1, /* First set of search criteria */
6                            "Origin", "IND", /* Second set of search criteria */
7                            "Dest", "NYC", /* Third set of search criteria */
8                            "Dest", "JFK", /* First column value to set */
9                            "Price", 75, /* Second value to set */
10                            "PerBagSurcharge", 10) /* Third value to set */
11  Set @upsert2 = UpsertData("Flights", /* Data extension that contains the data to upsert */
12                            4, /* Number of column and value pairs for the function to match against */
13                            "FlightId", 4, /* First set of search criteria */
14                            "FlightId", 4, /* Second set of search criteria */
15                            "FlightId", 4, /* Third set of search criteria */
16                            "FlightId", 4,  /* Fourth set of search criteria */
17                            "Origin", "JFK", /* First value to set */
18                            "Dest", "IND", /* Second value to set */
19                            "Price", 60, /* Third value to set */
20                            "PerBagSurcharge", 10) /* Fourth value to set */
21
22  Output(Concat("Upsert 1 complete. ", v(@upsert1)," rows added or updated."))
23  Output(Concat("Upsert 2 complete. ", v(@upsert2)," rows added or updated."))
24]%%

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.

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

• UpsertDE()