UpsertDE

by | Apr 21, 2018 | Book |

UpsertDE

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.

Arguments

UpsertDE(1,2,3,4,[5a,5b],6,7,[8a,8b]...)

Ordinal Type Required Description
1 String True Name of the Data Extension containing the rows to add or update
2 Number True Number of column and value pairs in the subsequent selection arguments
3 String True Column name used for selecting rows to add or update
4 String True Column value for selecting rows to add or update
5a String False Additional column name used for selecting rows to add or update (see note)
5b String False Additional column value for selecting rows to add or update (see note)
6 String True Column name to add or update
7 String True Column value to add or update
8a String False Additional column name to add or update (see note)
8b String False Additional column value to add or update (see note)

NOTE: Additional pairs of columns and values can be appended as arguments for selecting and upserting rows.

NOTE: This function only works in an email. Use UpsertData 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.

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

Example 1

The following illustrates the use of this function with the minimum number of arguments.

Data Extension: LoyaltyMembers

Name Data Type Length Primary Key Nullable Default Value
EmailAddress EmailAddress 254 N N
SubscriberKey Text 254 Y N
FirstName Text 50 N Y
LastName Text 50 N Y
Created Date N Y Current Date
Updated Date N Y

The LoyaltyMembers Data Extension includes these rows:

EmailAddress SubscriberKey FirstName LastName Created Updated
doug@limedash.com 8473 Doug Smith 2017-10-21 12:01
suzy@limedash.com 5497 Suzy Jackson 2017-10-20 11:01
dale@limedash.com 7114 Dale Cameron 2017-10-19 10:01

Here is an example utilizing the preceding context:

%%[

var @emailAddress, @newFirstName, @updatedDate

set @emailAddress = AttributeValue("emailaddr") /* value from attribute or DE column in send context */
set @emailAddress = "doug@limedash.com" /* or a literal value */

set @newFirstName = "Douglas"
set @updatedDate = Now(1)

UpsertDE("LoyaltyMembers",1,"EmailAddress", @emailAddress, "FirstName", @newFirstName, "Updated", @updatedDate)

]%%

Output

After the operation, the LoyaltyMembers Data Extension includes these row values:

EmailAddress SubscriberKey FirstName LastName Created Updated
doug@limedash.com 8473 Douglas Smith 2017-10-21 12:01 2017-10-22 14:01
suzy@limedash.com 5497 Suzy Jackson 2017-10-20 11:01
dale@limedash.com 7114 Dale Cameron 2017-10-19 10:01

Example 2


Not a subscriber? Subscribe now.

Example 3


Not a subscriber? Subscribe now.