Insert/ Update new rows from one data extension to another via query

Question

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!

Answer 1

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]