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.
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
%%[ var @emailaddress, @firstname, @lastname, @phone UpsertDE(‘Patient_Leads’,1,’Emailaddress’,@emailaddress,’FirstName’,@firstname,’lastname’,@lastname,’phone’,@phone) ]%%
(No Output. Data written in Data Extension)
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
UpsertDEfunction will update this record. If no matching email address is found, then the
UpsertDEfunction will insert a new record.
- ‘FirstName’ ,@firstname – The first field name/value pair to insert. The
UpsertDEfunction updates the first name.
- ‘lastname’,@lastname – The second field name/value pair to insert. The
UpsertDEfunction updates the last name.
- ‘phone’,@phone – The third field name/value pair to insert. The
UpsertDEfunction updates the phone number.
Related functions are the
UpsertData function, the
InsertDE function, and the