1

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.

2

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>
  • what if I want to use another DE as a lookup? I was thinking all the subscriber keys in the lookup needs deleting and only delete the matching records from the MASTER_DE – Peter Ho May 2 '18 at 15:43
  • @PeterHo see answer edits above – Gortonington May 2 '18 at 17:31
  • just tried this and it keeps failing but not getting any errors in automation studio <script runat="server"> var deleteDE = DataExtension.Init("PH_TEST_DE");//DE to delete from var data = deleteDE.Rows.Retrieve(); for (var i=0; i < data.length; i++) { var subKey = data[i].SubscriberKey; var rowDelete = Platform.Function.DeleteData('PH_SubscribersToDelete',['SubscriberKey'],[subKey]); // Look up DE }; </script> – Peter Ho May 2 '18 at 18:40
  • does it run without errors but subkeys not deleted, or does it error but you do not see any error messages in AS? – Gortonington May 2 '18 at 18:59
  • It just errors in AS without any errors so it doesnt delete anything – Peter Ho May 3 '18 at 7:29
0
<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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.