0

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!

5
  • I am not sure I fully understand, so gonna put my synopsis in hopes you can validate my understanding. So you have DE 1 which has CustomerIDs and data points and then DE 2 which has a list of CustomerIDs that need the change. You want to take DE 2 and write a script to iterate through these IDs (~10k) to push the changes to the Master list. Is this correct? Mar 22 at 17:37
  • Hey @Gortonington, yes. The Master List has a ton of data but 2 particular columns for 10,000 people need to be updated based on the Customer_ID, which I have stored as a separate DE in hopes I could use it to match on the Customer_ID column in the Master DE. I already had code to do this based on a typed out array (not referencing a DE) and it worked. I was hoping to keep the code 'as is' under "//UPDATES MASTER LIST" and just get the array part above to work with the 10,000-record Customer_ID DE.
    – Eduardo
    Mar 22 at 17:42
  • 1
    I believe SQL might be the better option for you instead of using scripts. From the sound of it the updates are the same values - just only on specific customerIDs Mar 22 at 17:52
  • 1
    Right, so it need to update say, "Banner Img 1 URL" and "Banner Img 2 URL" columns within the Master DE based on the Customer_ID, so long as that matches the list of Customer_IDs in the Customer_ID DE (the DE holding one column of 10,000 IDs). If they match, the 2 URLs get updated in the Master DE. I can try some SQL for it, I just already had this SSJS written to do "mass" updates to a column, so I was trying to work in an Array filter of sorts. I can try SQL though, if it seems like a better option. I'm still trying to sort out when to do one over the other.
    – Eduardo
    Mar 22 at 17:58
  • 1
    When it comes to data or ETL stuff, SQL is usually the top option as its much quicker. Mar 22 at 18:00

1 Answer 1

1

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.

2
  • I had to alter this a little, like I wasn't sure why "Select Top 1" was in there but it could have just been something lost in translation between my example/attempted description of the problem and the correct answer. Either way, I'm going to mark this as correct. Thank you so much for your assistance!
    – Eduardo
    Mar 22 at 19:26
  • SELECT TOP 1 was there to limit return in the exists to help reduce draw of the SQL Mar 22 at 20:40

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.