SQL query to update field only if it is empty

Question

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

Answer 1

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.