I have a data extension that I use for a newsletter. This newsletter will consist of a monthly automation to update the DE and a journey to send emails (12 months). My DE has a number field "sent_newsletters" that I want to update every month, so the first month is 1, then 2,3,etc until it reaches 12, when I stop sending emails. This field will be different for every record since they might enter the newsletter in different months (someone who enters in January would have 6 sent newsletters by June, compared to 2 for someone who entered in May).

My issue now is that I can't find a way to update this field for every individual record. A query would not work (Or would it? I haven't found a way to make it work.) since every record has a different value. Now I'm trying with a SSJS script as part of my automation. This works in principle but when looking at the documentation I find that "LookupRows" is limited to 2000 records, and "Rows.Retrieve()" is limited to 2500. This data extension will be in the order of tens of thousands so that would not work for me.

I found this similar question, they recommend using RetrieveRequest and ContinueRequest but it looks like it's not the best solution. I was hoping to have something simpler.

I'm new to SJSS and the requirement does not sound too complicated, but so far I have not been able to find a solution. How could I accomplish this?

  • 2
    I'm sure this sql will work as long as you don't have a null value select sent_newsletters + 1 as sent_newsletters from DE
    – EazyE
    Commented Feb 25, 2020 at 17:05
  • 1
    I agree that the SQL option should work if you wanted to add a step to the automation for a query activity that EazyE mentioned, as long as there is a Primary Key on the data extension.
    – Mac
    Commented Feb 25, 2020 at 18:21
  • As EazyE pointed out, a SQL (dataAction=update) will work if the DE has a primary key. Something like: SELECT <yourPrimaryKey>, COALESCE(sent_newsletters, 0) + 1 AS sent_newsletters FROM <yourDataExtension>
    – SebOB
    Commented Feb 26, 2020 at 7:53
  • It worked! The query was my first approach but I must have used the wrong syntax, because it didn't get past validation. Thank you all! Commented Feb 27, 2020 at 17:37


You must log in to answer this question.

Browse other questions tagged .