{"id":31480,"date":"2018-04-21T15:47:47","date_gmt":"2018-04-21T15:47:47","guid":{"rendered":"https:\/\/ampscript.guide\/upsertde\/"},"modified":"2022-04-05T12:22:25","modified_gmt":"2022-04-05T12:22:25","slug":"upsertde","status":"publish","type":"post","link":"https:\/\/ampscript.guide\/upsertde\/","title":{"rendered":"UpsertDE"},"content":{"rendered":"

UpsertDE<\/h2>\n

This function adds or updates the Data Extension row referenced in the column and value arguments. Values updated are specified with column name and value pairs. If no record matches the values specified, a new row is added. This function does not return a value.<\/p>\n

Arguments<\/h3>\n

UpsertDE(1,2,3,4,[5a,5b],6,7,[8a,8b]...)<\/code><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Ordinal<\/th>\nType<\/th>\nRequired<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nString<\/td>\nTrue<\/td>\nName of the Data Extension containing the rows to add or update<\/td>\n<\/tr>\n
2<\/td>\nNumber<\/td>\nTrue<\/td>\nNumber of column and value pairs in the subsequent selection arguments<\/td>\n<\/tr>\n
3<\/td>\nString<\/td>\nTrue<\/td>\nColumn name used for selecting rows to add or update<\/td>\n<\/tr>\n
4<\/td>\nString<\/td>\nTrue<\/td>\nColumn value for selecting rows to add or update<\/td>\n<\/tr>\n
5a<\/td>\nString<\/td>\nFalse<\/td>\nAdditional column name used for selecting rows to add or update (see note)<\/td>\n<\/tr>\n
5b<\/td>\nString<\/td>\nFalse<\/td>\nAdditional column value for selecting rows to add or update (see note)<\/td>\n<\/tr>\n
6<\/td>\nString<\/td>\nTrue<\/td>\nColumn name to add or update<\/td>\n<\/tr>\n
7<\/td>\nString<\/td>\nTrue<\/td>\nColumn value to add or update<\/td>\n<\/tr>\n
8a<\/td>\nString<\/td>\nFalse<\/td>\nAdditional column name to add or update (see note)<\/td>\n<\/tr>\n
8b<\/td>\nString<\/td>\nFalse<\/td>\nAdditional column value to add or update (see note)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n

NOTE: Additional pairs of columns and values can be appended as arguments for selecting and upserting rows.<\/p>\n

NOTE: This function only works in an email. Use UpsertData<\/a> if you need to add or insert rows into a Data Extension in a landing page, microsite page or CloudPage, or in an SMS message in MobileConnect.<\/p>\n

NOTE: According to the official order of processing<\/a>, upsert operations for the entire send are processed at the completion of the send. If your scripting utilizes a RaiseError<\/a> function that aborts an entire send, the upsert operation will not be completed.<\/p>\n<\/blockquote>\n

Example 1<\/h3>\n

The following illustrates the use of this function with the minimum number of arguments.<\/p>\n

Data Extension<\/strong>: LoyaltyMembers<\/em><\/p>\n\n\n\n\n\n\n\n\n\n\n
Name<\/th>\nData Type<\/th>\nLength<\/th>\nPrimary Key<\/th>\nNullable<\/th>\nDefault Value<\/th>\n<\/tr>\n<\/thead>\n
EmailAddress<\/td>\nEmailAddress<\/td>\n254<\/td>\nN<\/td>\nN<\/td>\n<\/td>\n<\/tr>\n
SubscriberKey<\/td>\nText<\/td>\n254<\/td>\nY<\/td>\nN<\/td>\n<\/td>\n<\/tr>\n
FirstName<\/td>\nText<\/td>\n50<\/td>\nN<\/td>\nY<\/td>\n<\/td>\n<\/tr>\n
LastName<\/td>\nText<\/td>\n50<\/td>\nN<\/td>\nY<\/td>\n<\/td>\n<\/tr>\n
Created<\/td>\nDate<\/td>\n<\/td>\nN<\/td>\nY<\/td>\nCurrent Date<\/td>\n<\/tr>\n
Updated<\/td>\nDate<\/td>\n<\/td>\nN<\/td>\nY<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The LoyaltyMembers<\/em> Data Extension includes these rows:<\/p>\n\n\n\n\n\n\n\n
EmailAddress<\/th>\nSubscriberKey<\/th>\nFirstName<\/th>\nLastName<\/th>\nCreated<\/th>\nUpdated<\/th>\n<\/tr>\n<\/thead>\n
doug@limedash.com<\/td>\n8473<\/td>\nDoug<\/td>\nSmith<\/td>\n2017-10-21 12:01<\/td>\n<\/td>\n<\/tr>\n
suzy@limedash.com<\/td>\n5497<\/td>\nSuzy<\/td>\nJackson<\/td>\n2017-10-20 11:01<\/td>\n<\/td>\n<\/tr>\n
dale@limedash.com<\/td>\n7114<\/td>\nDale<\/td>\nCameron<\/td>\n2017-10-19 10:01<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Here is an example utilizing the preceding context:<\/p>\n

%%[\n\nvar @emailAddress, @newFirstName, @updatedDate\n\nset @emailAddress = AttributeValue(\"emailaddr\") \/* value from attribute or DE column in send context *\/\nset @emailAddress = \"doug@limedash.com\" \/* or a literal value *\/\n\nset @newFirstName = \"Douglas\"\nset @updatedDate = Now(1)\n\nUpsertDE(\"LoyaltyMembers\",1,\"EmailAddress\", @emailAddress, \"FirstName\", @newFirstName, \"Updated\", @updatedDate)\n\n]%%<\/code><\/pre>\n
Output<\/h4>\n
After the operation, the LoyaltyMembers<\/em> Data Extension includes these row values:<\/p>\n\n\n\n\n\n\n\n
EmailAddress<\/th>\nSubscriberKey<\/th>\nFirstName<\/th>\nLastName<\/th>\nCreated<\/th>\nUpdated<\/th>\n<\/tr>\n<\/thead>\n
doug@limedash.com<\/td>\n8473<\/td>\nDouglas<\/td>\nSmith<\/td>\n2017-10-21 12:01<\/td>\n2017-10-22 14:01<\/td>\n<\/tr>\n
suzy@limedash.com<\/td>\n5497<\/td>\nSuzy<\/td>\nJackson<\/td>\n2017-10-20 11:01<\/td>\n<\/td>\n<\/tr>\n
dale@limedash.com<\/td>\n7114<\/td>\nDale<\/td>\nCameron<\/td>\n2017-10-19 10:01<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Example 2<\/h3>\n
\n\n\n\t\n\t
\n\n\t\t\n\t\t