0

I am attempting to automate our payment failures to alert consumers their subscription payment hasn't processed. My initial thought was to run a SQL activity once a day and inject new records into a payment journey that has a wait step at the end allowing them to reenter after a certain period of time. My question is, will the "Update" SQL activity removed records from the data extension when the requirement isn't satisfied?

For example, run query on 5/1 and payment failed. Run again on 5/2 and the payment has been resolved, will that user still be in the original table or removed? Here is the query i am working from:

SELECT pay.payment_status
    , pay.lead_id
    , pay.[Subscription Number]
    , pay.draft_date
    , pay.stop_notification
    , pay.Email
    , pay.Phone
    , pay.[Phone Formatted]
    , account.Status
FROM "Payment History" pay
    LEFT JOIN "Subscription Data" account
    ON account.[Subscription Number] = pay.[Subscription Number]
WHERE 
        pay.payment_status <> ''
        AND pay.payment_status <> ' ' 
        AND NOT pay.payment_status IS NULL
        AND LEN(pay.payment_status) > 0
        AND NOT pay.payment_status LIKE '%Approved%'
        AND account.Status in ('Active', 'ACTIVE')

My current work around is to overwrite the table every run, but, that isnt the ideal way to go.

4

Updates values in the target where field name matches field returned by SQL Query for rows whose Primary Key the SQL query returns. Rows returned by SQL Query whose Primary Key does not exist in the target are appended.

Use the SQL Query Activity

A query activity with an action of Update will not remove records from the data extension.

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.