sfmc.guide
Engagement Next
⌘K

UpsertDE

Inserts a row if no match exists, otherwise updates the matching rows, and returns an empty string. The email-context twin of UpsertData — the second argument is the search-pair count. Runtime-proven on a live Marketing Cloud Engagement CloudPage.

Runtime verified Test scripts included

Syntax

UpsertDE(dataExt, columnValuePairs, searchColumnName1, searchValue1[, searchColumnNameN, searchValueN, ...], columnToUpsert1, upsertedValue1[, columnToUpsertN, upsertedValueN, ...])  →  string

Parameters

Name Type Required Description
dataExt string Yes Name or external key of the data extension to upsert into
columnValuePairs number Yes Count of search column/value pairs that follow
searchColumnName1 string Yes First column to match on
searchValue1 string | number Yes Value the first search column must equal
searchColumnNameN string No Further search columns, each paired with a value
searchValueN string | number No Value for the corresponding further search column
columnToUpsert1 string Yes First column to write
upsertedValue1 string | number Yes Value for the first written column
columnToUpsertN string No Further columns to write, each paired with a value
upsertedValueN string | number No Value for the corresponding further column

Example

%%[
  UpsertDE("AMP_VERIFY_SCRATCH", 1, "Id", "D2", "FirstName", "Enzo", "Score", 8)
]%%

Renders nothing — UpsertDE returns an empty string. With no existing D2 row this takes the insert path; a second call with new values takes the update path. A later render reads back the current values (FirstName=[Enrico] Score=[88] after the second call).

The 1 after the data extension is the search-pair count — here one pair ("Id", "D2") decides insert-vs-update.

Return value

string — always an empty string, so nothing is emitted. This is the only runtime difference from UpsertData, which returns the affected-row count. Use the *DE family inside email sends; use the *Data family on CloudPages.

Behaviour

Insert-or-update on the search key. A match is updated; no match is inserted. This avoids the duplicate-key abort that InsertDE raises when the row already exists.

The second argument is the search-pair count — identical semantics to UpdateDE.

Writes are not visible to same-render reads of a row already read. AMPscript caches data-extension row reads within a single render; confirm the upsert in a subsequent render.

Show test script
%%[
  VAR @b, @r
  SET @b = RequestParameter("b")

  IF @b == "zzz" THEN
    OutputLine(Concat("CTRL=[alive]"))
  ENDIF

  IF @b == "insert" THEN
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "D2")
    SET @r = UpsertDE("AMP_VERIFY_SCRATCH", 1, "Id", "D2", "FirstName", "Enzo", "Score", 8)
    OutputLine(Concat("UpsertDE insert ret=[", @r, "]"))
  ENDIF

  IF @b == "update" THEN
    SET @r = UpsertDE("AMP_VERIFY_SCRATCH", 1, "Id", "D2", "FirstName", "Enrico", "Score", 88)
    OutputLine(Concat("UpsertDE update ret=[", @r, "]"))
  ENDIF

  IF @b == "read" THEN
    OutputLine(Concat("D2 FirstName=[", Lookup("AMP_VERIFY_SCRATCH", "FirstName", "Id", "D2"), "] Score=[", Lookup("AMP_VERIFY_SCRATCH", "Score", "Id", "D2"), "]"))
  ENDIF
]%%

Availability

Platform Available
Marketing Cloud Engagement Yes
Marketing Cloud Next No

See also