1

I'm trying to write a query that will take unsubscribes from a child BU and write into a shared DE at the parent level. I know how to find the unsubscribes, but I'm trying to write into a DE field where we store the "status" so I thought I would use a SET statement (SET brand_subscribed = 'false') but I keep getting an error at the SET statement.

Can I use a SET statement in a Query in Marketing Cloud?

I don't want to assign a default value of FALSE as I don't want to accidentally mark someone as unsubscribed if they are in another business unit (all unsubs for the parent are being written into the shared DE).

Improve this question

2 Answers 2

Reset to default
2

Salesforce Marketing Cloud queries currently use a variation of SQL 2008, and it does not allow some functions; such as SET/UPDATE/DELETE.

To achieve what you are trying to do, you can use SQL CASE to check the values from the _Subscribers Data View and write them into a new DE to export.

The following will 'Overwrite' into a new DE containing 3 values (Subscriberkey, Status and IsSubscribed):

SELECT
allsubs.SubscriberKey
,allsubs.Status
,CASE
    WHEN allsubs.Status = 'ACTIVE'
    THEN 'TRUE'
    ELSE 'FALSE'
    END as 'IsSubscribed'
FROM [_Subscribers] allsubs
Improve this answer
4
  • Thanks, Cameron! I gave it a go, and while the syntax was correct it never ended up writing to the ENT.DE that I selected... so, back to plan A (which is really a plan B!) of importing into a DE with a default value of "FALSE" and moving that data around.
    – George Rosedale
    Commented Mar 26, 2017 at 20:12
  • Hey @GeorgeRosedale, post a new question and include your query (and as much detail as possible) and the community can try to help you.
    – Cameron Robert
    Commented Mar 26, 2017 at 23:12
  • Here's what I tried: SELECT SubscriberKey as email, DateUnsubscribed as BMT_unsubscribe_date, Status, CASE WHEN Status = 'UNSUBSCRIBED' THEN 'FALSE' ELSE 'TRUE' END as 'BMT_subscribed' FROM _Subscribers WHERE DATEPART(mm,DateUnsubscribed) = DATEPART(mm,DateAdd(d,-1,GetDate())) AND DATEPART(dd,DateUnsubscribed) = DATEPART(dd,DateAdd(d,-1,GetDate())) AND DATEPART(yyyy,DateUnsubscribed) = DATEPART(yyyy,DateAdd(d,-1,GetDate()))
    – George Rosedale
    Commented Apr 2, 2017 at 1:25
  • Were you able to make it work George? You can try using a BETWEEN operator in your WHERE clause to make it easier. See: w3schools.com/sql/sql_between.asp
    – Cameron Robert
    Commented Apr 2, 2017 at 2:39
0

In short, you can not use any update statements in marketing cloud query activity but if your only goal is to collect unsubscribes from BU to a shared DE then I am not sure where you are actually getting this problem as you can simply use _subscribers data view and get subscribers data in to result DE.

_subscriber data view

Improve this answer
1
  • Thank you for the reply. What I'd like to do is change a value from TRUE to FALSE for the export (which is how sub flags are stored outside of MC)
    – George Rosedale
    Commented Mar 24, 2017 at 14:11

You must log in to answer this question.

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