trying to clear the records in a DE using a query in Automation Studio but it's not clearing the data. Am I doing this wrong?

,[Activity 18 Digit ID]
,[Hidden Contact 18 Digit ID]   
 FROM [Recruitment Contacts Booking Attended]
 WHERE 1 = 0
  • 2
    Are you using Overwrite option in your query? Dec 9 '20 at 14:55
  • no am not, What would that look like, and should I be? thx for responding so quickly Dec 9 '20 at 15:15

When creating your Query Activity in Automation Studio, you want to select the Overwrite option in the step where you select your target data extension: enter image description here This will ensure that the empty result of your query will overwrite the records in the target data extension with 0 rows.

To avoid errors (as you are trying to read from same data extension as you are overwriting), you can use this smart trick, which Adam Spriggs once taught me:

SELECT null as Email,
null as [Activity 18 Digit ID],
null as [Hidden Contact 18 Digit ID]
WHERE 1 = 0
  • when I switch it to overwrite I get an error:Error saving the Query field.Update Type must be 'Update' or the target data extension 'Recruitment Contacts Booking Attended' cannot appear in the query. Dec 9 '20 at 16:06
  • My bad - forgot to account for you are reading from same DE as you are overwriting. Please see my updated answer Dec 9 '20 at 16:12
  • Perfect, works great. thank you so much for your help. I saw that null syntax in another post but I didn't realize I needed to select overwrite Dec 9 '20 at 16:31

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.