0

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.

1 Answer 1

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).

2
  • Thank you so much for your reply. Since this uses Add/Update action, will it add records from DE2 that don't currently exist on DE1?
    – NAM
    Dec 6, 2018 at 14:06
  • I added in some more explicit logic to help prevent this as well as turning it from an INNER JOIN into a LEFT JOIN. This should remove the possibility of any new records from DE2 being inserted into DE1 Dec 6, 2018 at 15:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .