--- schema_version: "1" id: 896da4f8c77d9aa1da5514924cde6b4504cd85db10f500b7298c8b8ee184f621 title: UpsertDE() collection: developer version: current locale: en-us portal_url: https://docs.salesforce.com/developer/current/en-us/marketing-cloud/ampscript/references/data-extension/mc-ampscript-reference-data-extension-upsert-de.md canonical_url: https://developer.salesforce.com/docs/marketing/marketing-cloud-ampscript/references/mc-ampscript-data-extension/mc-ampscript-reference-data-extension-upsert-de.html source_site: developer.salesforce.com content_type: documentation content_hash: 1bf50d78e15b6f976d80b1405e628b38ab29c0c3f863a80167f1cc25d2c6a914 products: - AMPscript for Marketing Cloud - Marketing Cloud --- # 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()](https://developer.salesforce.com/docs/marketing/marketing-cloud-ampscript/references/mc-ampscript-data-extension/mc-ampscript-reference-data-extension-upsert-data.md) function to update or insert data extensions in landing pages, microsites, and SMS messages in MobileConnect. ## Availability
Marketing Cloud Engagement ✅ Yes
Marketing Cloud Next ❌ No
## Syntax ``` UpsertDE(dataExt, columnValuePairs, searchColumnName1, searchValue1, [searchColumnName2, searchValue2,] columnToUpsert1, upsertedValue1, [columnToUpsert2, upsertedValue2]) ``` 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. ## Usage 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. | FlightId | Origin | Dest | Price | PerBagSurcharge | | -------- | ------ | ---- | ----- | --------------- | | 1 | IND | NYC | 100 | | | 2 | IND | LAX | 200 | | | 3 | IND | SEA | 500 | 25 | This function upserts data for `FlightId` 3 and `FlightId` 4. ``` %%[ UpsertDE("Flights", /* Data extension that contains the data to upsert */ 3, /* Number of column and value pairs for the function to match against */ "FlightId", 1, /* First set of search criteria */ "Origin", "IND", /* Second set of search criteria */ "Dest", "NYC", /* Third set of search criteria */ "Dest", "JFK", /* First value to set */ "Price", 75, /* Second value to set */ "PerBagSurcharge", 10) /* Third value to set */ UpsertDE("Flights", /* Data extension that contains the data to upsert */ 4, /* Number of column and value pairs for the function to match against */ "FlightId", 4, /* First set of search criteria */ "FlightId", 4, /* Second set of search criteria */ "FlightId", 4, /* Third set of search criteria */ "FlightId", 4, /* Fourth set of search criteria */ "Origin", "JFK", /* First value to set */ "Dest", "IND", /* Second value to set */ "Price", 60, /* Third value to set */ "PerBagSurcharge", 10) /* Fourth value to set */ ]%% ``` 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. | FlightId | Origin | Dest | Price | PerBagSurcharge | | -------- | ------ | ---- | ----- | --------------- | | 1 | IND | JFK | 75 | 10 | | 2 | IND | LAX | 200 | 25 | | 3 | IND | SEA | 500 | 25 | | 4 | JFK | IND | 60 | 10 | ## Errors and Troubleshooting 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. ## See Also - [UpsertData()](https://developer.salesforce.com/docs/marketing/marketing-cloud-ampscript/references/mc-ampscript-data-extension/mc-ampscript-reference-data-extension-upsert-data.md)