[SalesForce] can I use a SET statement in a Marketing Cloud QUERY

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

Best Answer

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
Related Topic