SSJS to delete rows from a data extension via a lookup table

Question

Is there a way to delete selected rows from a Data extension(MASTER_DE) by looking up another data extension as a lookup (to identify the subscriber keys that need to be deleted) using SSJS?

I have found some blogs to delete all the data but not selected rows.

Basically, I have a client who doesn't have data retention policy applied to DE's and you can't add data retention policy once it's created. so I was trying to create a way to delete any records that are older than 3 months through an automation.

Answer 1

Good news! You CAN actually change the data retention properties on an existing DE.

You just need to create an Update SOAP API call to the DataExtension Object and use the DataRententionPeriod, DataRetentionLength and DeleteAtEndOfRetentionPeriod properties.

As far as your requested use case, you can easily do that as well using something like below.

<script runat="server">

     var masterDE = DataExtension.Init("MASTER_DE");
     var filter = {Property:"Date",SimpleOperator:"greaterThan",Value: yourDate};
     var data = masterDE.Rows.Retrieve(filter);

     for (var i=0; i < data.length; i++) {

       var subKey = data[i].SubscriberKey;

       var rowDelete = Platform.Function.DeleteData('MASTER_DE',['SubscriberKey'],[subKey]);

    };
</script>

---

Based on your comments, it seems you are going to store all the items that need deletion in another DE. In that case it is even easier:

<script runat="server">

     var deleteDE = DataExtension.Init("yourDELETE_DE");
     var data = deleteDE.Rows.Retrieve();

     for (var i=0; i < data.length; i++) {

       var subKey = data[i].SubscriberKey;

       var rowDelete = Platform.Function.DeleteData('MASTER_DE',['SubscriberKey'],[subKey]);

    };
</script>

Answer 2

<script runat="server">

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

     var deleteDE = DataExtension.Init("yourDELETE_DE External Key");
     var data = deleteDE.Rows.Retrieve();

     for (var i=0; i < data.length; i++) {

       var subKey = data[i].SubscriberKey;

       var rowDelete = Platform.Function.DeleteData('MASTER_DE',['SubscriberKey'],[subKey]);

    };
</script>

the above code works fine. We need to add Platform.Load("Core", "1"); and delete DE external key as mentioned in above code.