Update field to Null with SSJS

Question

I'm trying to update a field in a data extension to NULL for everyone in the DE. I am new to SSJS and tried to use a script from somewhere else but am struggling to make it work. Can anyone help troubleshoot?

<script runat="server">

Platform.Load("Core","1.1.1");

// 1. Data Extension Configuration
var dataExtensionName = "ExampleDEName"; 
var mobileNumberFieldName = "MobileNumber";
var subscriberKeyFieldName = "SubscriberKey"; 

// 2. Retrieve All Contacts
var dataExtension = DataExtension.Init(dataExtensionName);
var allContacts = dataExtension.Rows.Retrieve();

// 3. Update Contacts (Set Mobile Number to null)
for (var i = 0; i < allContacts.length; i++) {
    var contactKey = allContacts[i].CustomerKey; 
    var contactSubscriberKey = allContacts[i][subscriberKeyFieldName]; // Get the Subscriber Key value
    var updateData = {};
    updateData[mobileNumberFieldName] = null; 

    var status = dataExtension.Rows.Update([subscriberKeyFieldName], [contactSubscriberKey], updateData);

    // Error Handling 
    if (!status) {
        Write("<br>Error updating contact with Subscriber Key: " + contactSubscriberKey);
    } else {
        Write("<br>Updated contact with Subscriber Key: " + contactSubscriberKey + ", Status: " + status); 
    }
}

Write("<br>Update process complete.");

</script>

WSProxy may be the only method for doing this: salesforce.stackexchange.com/a/227615/5202 — Adam Spriggs
– Adam Spriggs, Commented Sep 7, 2024 at 16:02

Niko · Accepted Answer · 2024-09-07 16:46:02Z

You are on the right track but you have several issues with your code:

First, you need to check if you are using Data Extension's External Key and not its Name. I fixed variable and placeholder values for that.
Also, I fixed the Rows.Update function. You need to manually specify there which field you want to update as a first parameter, in this case, it is MobileNumber with a null value and only then set a pair of arrays that will identify which row to update - SubscriberKey field with retrieved SubscriberKey value.

<script runat="server">

Platform.Load("Core", "1");

// 1. Data Extension Configuration
var dataExtensionExternalKey = "ExampleDEExternalKey"; 
var subscriberKeyFieldName = "SubscriberKey"; 

// 2. Retrieve All Contacts
var dataExtension = DataExtension.Init(dataExtensionExternalKey);
var allContacts = dataExtension.Rows.Retrieve();

// 3. Update Contacts (Set Mobile Number to null)
for (var i = 0; i < allContacts.length; i++) {
    var contactKey = allContacts[i].CustomerKey; 
    var contactSubscriberKey = allContacts[i][subscriberKeyFieldName]; // Get the Subscriber Key value
    var status = dataExtension.Rows.Update({MobileNumber: null}, [subscriberKeyFieldName], [contactSubscriberKey]);

    // Error Handling 
    if (!status) {
        Write("<br>Error updating contact with Subscriber Key: " + contactSubscriberKey);
    } else {
        Write("<br>Updated contact with Subscriber Key: " + contactSubscriberKey + ", Status: " + status); 
    }
}

Write("<br>Update process complete.");

</script>

Rows.Retrieve will only return 2500 rows: developer.salesforce.com/docs/marketing/marketing-cloud/guide/… — Adam Spriggs
– Adam Spriggs, Commented Sep 7, 2024 at 16:55
@AdamSpriggs Yep, I know. Just decided not to overcomplicate the code since the guy is new to SSJS as he states. — Niko
– Niko, Commented Sep 7, 2024 at 18:02

Adam Spriggs · Accepted Answer · 2025-03-12 14:50:04Z

Here's how I approach updating Data Extension rows via Script Activity. Using WSProxy (SOAP), you can iterate through all of the matching rows by page.

I use a Processed flag that's defaulted to false on each row to keep track of progress, that way you can stack these scripts in an Automation if a single instance takes more than 30 minutes to process.

If you're having issues, set the debug flag to true and execute it in a CloudPage to observe the errors from the try/catch.

NOTE: You'll need to update the YOURDEKEYHERE with the customer/external key of your Data Extension.

<script runat="server" language="javascript">

Platform.Load("core", "1");

var debug = false;
var sourceDEName = "ExampleDEName"
var sourceDEKey = "YOURDEKEYHERE";
var sourceDE = DataExtension.Init(sourceDEKey);

try {

  var prox = new Script.Util.WSProxy();
  var objectType = "DataExtensionObject[" + sourceDEKey + "]";
  var cols = ["CustomerKey", "SubscriberKey", "MobileNumber", "Processed"];
  var filter = {Property: "Processed", SimpleOperator: "equals", Value: false};

  var opts = { BatchSize: 1000 };
  var props = {};

  var moreData = true;
  var reqID = null;

  if (debug) { Write("<br>filter: " + Stringify(filter)); }

  var abort = false;

  while (moreData && !abort) {

      moreData = false;

      var data = reqID == null ? prox.retrieve(objectType, cols, filter, opts, props) : prox.getNextBatch(objectType, reqID);

      if (data != null) {

          moreData = data.HasMoreRows;
          reqID = data.RequestID;

          if (debug) {
            Write("<br><br>moreData: " + moreData);
            Write("<br>reqID: " + reqID);
          }

          if (data && data.Results) {

            for (var i=0; i < data.Results.length && !abort; i++) {

                var row = data.Results[i];

                var CustomerKey = row.Properties[0].Value;
                var SubscriberKey = row.Properties[1].Value;
                var MobileNumber = row.Properties[2].Value;

                if (debug) {
                  Write("<hr>CustomerKey: " + CustomerKey);
                  Write("<br>SubscriberKey: " + SubscriberKey);
                  Write("<br>MobileNumber: " + MobileNumber);
                }

                try {

                   var row2 = {};
                   row2.Processed = true;
                   row2.MobileNumber = null;

                   if (debug) {
                     Write("<br>row2: " + Stringify(row2));
                   }

                   var rowsUpdated = sourceDE.Rows.Update(row2, ['SubscriberKey'], [SubscriberKey]);

                   if (debug) {
                      Write("<br>rowsUpdated: " + rowsUpdated);
                   }

                } catch (e) {

                   if (debug) {
                     Write("<br>update row e: " + Stringify(e));
                   }

                }

                if (debug && i > 5) {
                    abort = true;
                }

            } // for loop 

          } // results

      } // data 

  } // while loop

} catch (e) {

  if (debug) {
    Write("<br>e: " + Stringify(e));
  }

}

</script>

Stack Exchange Network

Update field to Null with SSJS

2 Answers 2

You must log in to answer this question.

Linked

Hot Network Questions

Update field to Null with SSJS

2 Answers 2

You must log in to answer this question.

Linked

Related

Hot Network Questions