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 theUpsertDE
function will update this record. If no matching email address is found, then theUpsertDE
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.