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.

Improve this question

1 Answer 1

Reset to default
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).

Improve this answer
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? Commented 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 Commented Dec 6, 2018 at 15:02

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.