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

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 '18 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 – Gortonington Dec 6 '18 at 15:02

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.