5

I have a query in a child business unit with an action type of 'Append.'

This query contains a join, and it's targeting one of the Data Extensions referenced in the join. The query was originally targeting and joining on a Shared Data Extension and I had no issue saving or running the activity.

However, I've since changed the target and joined DE to one contained within the child business unit. When I try to save the query activity after targeting the new data extension, I'm given the error:

Type must be 'Update' or the target data extension 'DE' cannot appear in the query.

Why can I only append a data extension that's also referenced in the query when it's a shared DE?

2

This was likely a bug or something in SFMC where it did not recognize that the shared DE referenced was the same as the target. (perhaps something to do with the ent. prefix? not sure) The policy for all SFMC BUs is that if a Query references the target DE inside of it, it MUST be update only. Personally I find this to be a very useful safeguard even though I know for the more advanced it can be frustrating at times

Now, Update does not mean new records will not be added. Update in SFMC SQL Query Activities is actually Add and Update. So if you set your query right to ensure it only collects new records to the target, you will still have it essentially being an 'append only' query.

  • 1
    Thanks, @gortonington. Your explanation makes sense. I'll avoid self-appends from now on. I believe I found a solution to use Update in conjunction with a 'Not Exists' clause to essentially achieve an append. – ropedog Nov 5 '19 at 15:20

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.