0

I am new to SQL and I would like to know how can I insert/update rows in a specific column (e.g only email address) to another data extension via query.

What I am trying to do is to update any new email addresses from a filtered data extension to the All Subscribers List. Hence the columns that I am interested in are: "Email_Address__c" from "FilteredDE" as well as "SubscriberKey" and "EmailAddress" from "_subscribers".

I looked up on sql forums and most of them suggested using INSERT INTO. However when I tried using it, Salesforce Marketing Cloud doesn't allow INSERT INTO to be used.

Can anyone give me any advice on this?

Thanks!

0

First of all, I don't think you can use Query to update All Subscriber Lists, as query only works with Data Extensions (local or shared), system data extensions can not be selected (_Sent, _Open etc)

And You can't use insert , delete or update keyword in Marketing Cloud query, it is not standard TSQL, to implement "insert into" you have to use select query and include the PK column. Marketing cloud uses the dataset from select query to match and update target data extension rows, that is why PK column must be configured in targeted DE to allow "Append" or "Update" action.

"Data actions" on targeted Data Extension

  1. Append - only add new data

  2. Update - only update existing data

  3. Overwrite - delete all records in target data extension and insert all records from your query.

so an update query should look like this

select Email_Address__c as 'EmailAddress',
Email_Address__c as 'SubscriberKey'
From [FilteredDE]

then select targeted data extension and "Update" type. Assume that your targeted data extension has SubscriberKey column set to PK, above query will find records that match SubscriberKeys and update EmailAddress column to values in [FilteredDE].[Email_Address__c]

  • Thanks for your response, Bo Hu. Actually I wanted to do the other way round. I am intending to update or insert new rows into All Subscribers List based on any new email addresses added to the filtered DE. In Query, I am not able to select All Subscribers as my target. – Valarie Simmons Nov 16 '17 at 5:54
  • @ValarieSimmons You are right - you can't select all subscribers list as target list/DE. You can try using Ampscript to loop through FilteredDE, and update Subscriber's Email address by matching SubscriberKey – Bo Hu Nov 17 '17 at 0:14

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.