2

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 Answers 2

5

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>
8
  • 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, 2018 at 15:43
  • @PeterHo see answer edits above May 2, 2018 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, 2018 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? May 2, 2018 at 18:59
  • 1
    @Chris, that is why I usually recommend using the same value for both external key and name. It helps to make inconsistencies like that in the platform less impactful. Jul 28, 2023 at 13:23
1
<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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .