0

we have a data extension which needs to get updated from a real time sync through salesforce, Right now we are using a Staging D.E to store the data and then update it to our desired D.E. But the problem is sometimes we will have records which are just updates to the existing primary key. When the new records have null values we lose our already existing records. Is there any way we can only update the D.E with non-null values. Any help would be appreciated. Thank you.

1 Answer 1

1

You would essentially need to join the staging DE an the Target DE to have access to values from both data extensions and then use the SQL Case statement to compare the values.

SELECT
    s.PrimaryKey,
    CASE
        WHEN s.Field is null THEN t.Field
        ELSE s.Field
    END as Field
FROM
    StagingDE s
JOIN
    TargetDE t
ON 
    s.PrimaryKey = t.PrimaryKey
4
  • Thank you so much it really works, but I have around 137 fields. So, I have to do a case statement individually for all the fields, is there any workaround for that? Thank you.
    – Kumar
    Jul 30, 2019 at 17:36
  • I have been trying it but I get an error, I have one more concern does it also append any new data or only update the existing one's? Do you know how can I add new records also if the primary key doesn't exist in the Target D.E
    – Kumar
    Jul 30, 2019 at 21:09
  • I don't know a workaround for those multiple case statements. You're right, this would only update and not append new data, but the fix is simple - replace the JOIN with a LEFT JOIN. This way records with primary keys that do not exist in the TargetDE will be also added. Jul 31, 2019 at 7:50
  • Thank you, I created cases for 137 fields and also did a left join as you mentioned and was able to update and append it at the same time
    – Kumar
    Jul 31, 2019 at 20:04

You must log in to answer this question.

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