SSJS Lookup All Rows from Data Extension Column as Array

datadataextensionsssjs

So I have a "Master" Data Extension that has a ton of columns and rows (millions), and one column needs to have some changes made for specific rows based on Customer_ID. I have a Customer_ID list with just the ID for those who need the change, and I have the Master list with a Customer_ID column. What I was hoping to do is use some previously written SSJS to update/edit a particular column's data based on the Customer_ID DE matching the Customer_ID within the Master DE. I've searched the site and cannot find something exactly like what I am looking for. Here's what I've tried so far in Automation Studio's Script Activity:

<script runat="server">
// LOOKUP VALUES FROM DE AND ASSIGN TO ARRAY TO LOOP IN UPDATE SECTION
    var myDE = DataExtension.Init('Customer_ID_DE');
    var custIdArray = myDE.Retrieve({Property:"customer_id",SimpleOperator:"greaterThan",Value:"0"});
    // commented out but displaying another attempt: var custIdArray = myDE.Rows.Lookup(["customer_id"]);
    
// ESTABLISH LOOP
    for (var i = 0; i < custIdArray.length; i++) {
    
    // UPDATES MASTER LIST
    //1. DE Name 
    //2. Column Name as Key
    //3. Column Values Filter (not req but can replace targeted values) 
    //4. Column Name for Update 
    //5. Column Value to Update (like new URL)
        var rows = Platform.Function.UpdateDE("Master_DE",["customer_id"],[custIdArray[i]],["Image Column","Link Column"],["New Image URL","New Link URL"]);

I tried to comment the code as best I could to explain what I'm trying to do. I've been able to type out the array in the past when I need to change things based on 10 or 20 customer IDs, but this particular change is a list of 10,000 customer IDs – and I don't want to type that out. My concern with some of the documentation I've looked at is that it seems limited to 2500 records with some functions, so I'm hoping for some professional advice as to how to approach this. The ideal is to replace the data in the Data Extension instead of manipulating the email template with AMPScript, and the reason behind that is that the client will then assume we can manipulate the template in any way we want, creating more work week-over-week. It is much less work to have a DE I can overwrite with Customer_IDs whenever I need to do something like this and use that DE as a lookup/find-and-replace by putting it in an array and running this code. I'm open to ideas and other options though, and any help is really appreciated!

I think I've provided all the info required, but of course feel free to ask questions as well. Thank you all so much!

Best Answer

You might want to explore doing a SQL query instead.

Something like:

SELECT m.ContactID,
  'https://myurl.com' as url,
  'mysecondfield' as field2,
  ...all the other fields...
FROM [MasterDE] m
WHERE EXISTS (SELECT TOP 1 a.ContactID FROM [DE2] a WHERE a.ContactID = m.ContactID)

And use the action of Update so it will set the changes for url and field2 for all records that exist in both DEs but will make no changes to other existing records in the Master DE that do not exist in the second DE.

Related Topic