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!
Best Answer
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
Append - only add new data
Update - only update existing data
Overwrite - delete all records in target data extension and insert all records from your query.
so an update query should look like this
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]