Error appending a Data Extension referenced in query

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?

Answer

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.

Attribution
Source : Link , Question Author : ropedog , Answer Author : Gortonington

Leave a Comment