0

I need to run a query which updates a target DE with some rows from another. There is a field called 'origin' which shows on the target data extension where the data came from.

I appreciate this may be more of a generic SQL query but given Marketing Cloud's limited implementation of SQL and some functions being unavailable, I thought I'd ask here...

Basically it will be updating records in the target data extension based on the primary key (subscriber key). Some data exists on various source data extensions. I only want it to fill in the 'origin' field with a value if the field in the target DE is empty/null. If it already has a value in it, I want it left as it is.

I looked at case statements but could only find examples where the decision was made based on a value being in the source DE, rather than the target DE.

For example, I have the current DE:

SubscriberKey Audience
abc APP21
xyz

I'm running queries that pick records up from various DEs and there will be duplication. I'd like to run a query that updated the blank value in the xyz record with 'APP20', but I do not want to overwrite the abc record's 'APP21' value with 'APP20'.

3
  • Please update your question to include some sample data and the desired output. Feb 26, 2022 at 14:03
  • 1
    By DE you mean Data Extensions?
    – codeulike
    Feb 28, 2022 at 12:06
  • @codeulike yes.
    – TCassa
    Feb 28, 2022 at 14:51

1 Answer 1

0

If the sole purpose of your query is to update the 'origin' field, then you can use NOT IN statement of SQL with nested query.

for example

SELECT 
      SubscriberKey,
      field1,
      field2,
      ..
      fieldN
FROM SourceDE
WHERE SubscriberKey NOT IN (SELECT DISTINCT SubscriberKey
                            FROM TargetDE
                            WHERE origin IS NOT NULL)

This will exclude the subscribers whose 'origin' field is not null/empty from theresult set. As long as you are updating the target DE based on a particular SubscriberKey, only intended rows will be updated.

Hope this helps.

You must log in to answer this question.

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