Inserting a value into a field in a Data Extension where records match on a key in Salesforce Marketing Cloud

Question

Please excuse my ignorance, as this seems like a simple thing. I'm just new to to SQL and am having trouble working it out.

I have a data extension of records with a blank Variable field. I have a second data extension with a shared key with the first data extension. I want to write a SQL activity to use in an automation to update the variable field in DE 1 to a specific value ("Match") where they match on the key in DE 2. I've tried to look it up, but most of my googling leads me to INSERT, which i don't think is possible in SFMC.

Thanks in advance for the help.

Answer 1

You need to do a join and a manual assignment of value into a column.

The below will take all the existing records in DE1 and then 'filter' out those that match with key in DE2 and then add a value of 'Match' into the 'Variable' field. This will then take those filtered records with the new value and 'upsert' them into your DE1 data extension - filling in 'Match' only to those records that were a match.

SELECT a.pkey /* Where pkey is your primary key
       , 'Match' as 'Variable' 
       /* This will push 'Match' into the field named 'Variable' */
FROM [DE1] a
LEFT JOIN [DE2] b
ON a.pkey = b.pkey
WHERE b.pkey IS NOT NULL /* Not needed, but I like to have extra explicit logic */
AND a.pkey IS NOT NULL

/* Action: Update */
/* Target: DE1 */

Notice the Action and Target above. You need to ensure you use these when creating the SQL Query Interaction. Targeting a DE that is inside of the query requires you to always use the Action of Update (which is really Add/Update).