The UpsertData 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 UpsertData function is for CloudPages, Microsites, and SMS messages in MobileConnect and not to be confused with the UpsertDE function which is for email sends only.

A simple use case for using the UpsertData function is to write to a data extension after a form is filled out on a CloudPage.

Syntax:

UpsertData(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

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

Output:

(No Output. Data written in Data Extension)

Explanation:

The UpsertData 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 UpsertData function will update that record. In the example above, the number “1” is used in the UpsertData 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 UpsertData 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 UpsertData function will update this record. If no matching email address is found, then the UpsertData function will insert a new record.
• ‘FirstName’ ,@firstname – The first field name/value pair to insert. The UpsertData function updates the first name.
• ‘lastname’,@lastname – The second field name/value pair to insert. The UpsertData function updates the last name.
• ‘phone’,@phone – The third field name/value pair to insert. The UpsertData function updates the phone number.

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