UpsertDE Function

The UpsertDE function updates an existing row in a data extension (or inserts a row if no matching row is found) by using name value pairs of field name and value. Note that the UpsertDE function is for email sends and not to be confused with the UpsertData function which is for CloudPages, microsites, and SMS messages in MobileConnect.

A simple use case for using the UpsertDE function is to write to a data extension the details of a send so that it can be filtered or queried later — similar to a SendLog but with more customizable data included in the data extension.

Syntax:

                    

UpsertDE(1,2,3,4,5,6)
1 = Name of data extension to update the existing row or insert if no matching row is found
2 = Number of fields/columns to evaluate using a WHERE clause
3 = Field/column name that is used to evaluate in the WHERE clause
4 = Value of field/column used to evaluate in the WHERE clause
5 = Field/column name that is to be inserted into the data extension
6 = Value of field/column that is to be inserted into the data extension

                

Example:

                    

%%[
var @emailaddress, @firstname, @lastname, @phone

UpsertDE(‘Patient_Leads’,1,’Emailaddress’,@emailaddress,’FirstName’,@firstname,’lastname’,@lastname,’phone’,@phone)
]%%


                

Output:

(No Output. Data written in Data Extension)

Explanation:

The UpsertDE function is used update data in the “Patient_Leads” data extension. If there is an existing record in the data extension which matches on the field “Emailaddress” then the UpsertDE function will update that record. In the example above, the number “1” is used in the UpsertDE function that only one field is to be used to evaluate the where clause and this is using the “Emailaddress” field. Then the rest of the fields in the function are used to insert into the data extension. Here is the break down of the example UpsertDE function above:

• “Patient_Leads” – The name of the data extension where data will be updated or inserted
• 1 – Only one column will be used to evaluate using the WHERE clause
• Emailaddress – If the email address in the email send matches an existing email address in the “Patient_Leads” data extension, then the UpsertDE function will update this record. If no matching email address is found, then the UpsertDE function will insert a new record.
• ‘FirstName’ ,@firstname – The first field name/value pair to insert. The UpsertDE function updates the first name.
• ‘lastname’,@lastname – The second field name/value pair to insert. The UpsertDE function updates the last name.
• ‘phone’,@phone – The third field name/value pair to insert. The UpsertDE function updates the phone number.

Related functions are the UpsertData function, the InsertDE function, and the UpsertDE function.