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).

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
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. Mar 26 '17 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. Mar 26 '17 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())) Apr 2 '17 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 Apr 2 '17 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

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) Mar 24 '17 at 14:11

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.