I'm trying to update a DE in marketing cloud based on a query in Automation Studio. It validates and appears to run when I click Run Once but my target DE never updates any records. I'm relatively new to SQL and Marketing Cloud so bare with me. This is probably a basic query but this is a test to expand on later.

a.order_number as [TRANSACTION_KEY]
ON c.TRANSACTION_KEY = a.order_number

TEST_PI is a new DE with two fields, both which are blank. My expected result is to take order_number values from DE IGO_PURCHASES and populate TRANSACTION_KEY values with those queried results.

Like I said, it runs, but TEST_PI records remain at 0. Any help would be greatly appreciated.

example of the table structure for IGO_PURCHASES

Name             Data Type   Length   Primary Keys   Nullable   Default Value
Sku              Text        256      YES            NO         N/A
order_number     Text        256      NO             YES        N/A
user_id          Text        256      YES            NO         N/A
Timestamp        Date        N/A      YES            NO         N/A

example of the table structure for TEST_PI

Name             Data Type   Length   Primary Keys   Nullable   Default Value
TRANSACTION_KEY  Text        256      YES            NO         N/A
PRODUCT_KEY      Text        256      NO             YES        N/A
  • It looks like order number and transaction key are probably not the same value? If so, why are you joining on them? What is the primary key in your second table? May 3, 2018 at 19:30
  • So I'm probably doing this wrong. The primary key in the DE I want to update is TRANSACTION_KEY. Maybe JOIN isn't the correct command. May 3, 2018 at 20:30
  • Joining is done on matching values (not necessarily primary keys). Primary keys are used for unique fields to a record and cannot occur on multiple records within a data extension. eg. order_number or client_id. May 3, 2018 at 21:03
  • Ok, that explains why it's not doing what I had hoped. What I am ultimately trying to do is get the value of order_number from the DE called IGO_PURCHASES and populate a field called TRANSACTION_KEY in the DE called TEST_PI. May 4, 2018 at 12:53
  • can you post an example of your table structure for each table? May 4, 2018 at 13:04

1 Answer 1


Your query is actually simpler than you made it. This is based on the assumption that you just want to move the data 'order_number' from IGO_PURCHASES to TEST_PI and relabel as TRANSACTION_KEY.

order_number as [TRANSACTION_KEY]

You then will utilize either 'UPDATE' (ADD and UPDATE) or 'OVERWRITE' option and have your target DE be 'TEST_PI'.

  • Actually what you have is exactly the last version I had tried after posting here and I still get 0 results. Would this query pull ALL of the order_number values from all of the records in IGO_PURCHASES and build new records with these values in the TRANSACTION_KEY field within the TEST_PI DE? I hope that makes sense. May 4, 2018 at 16:18
  • Yes this should pull over everything with an order_number into the new DE. I would verify a.) you have data in the source DE (more specifically in the order_number column) b.) your target DE is TEST_PI c.) your query has finished running. I also have found sometimes in SFMC running the first query to an empty DE will provide 0 results unless it is set as 'Overwrite'. It is a rare anomaly, but if you haven't already, try setting it for the first time as overwrite then changing it back after the initial run. May 4, 2018 at 17:00
  • Something on our end must be preventing it. I'm looking in the Activity log and every time I've run it I get the following failed error: Automation failed due to system error. May 4, 2018 at 18:58
  • Check the interactions tab in AS and find your query, there should be an action log there that may give better insight May 4, 2018 at 20:31
  • 1
    Thank you for all of your help. I built out the entire automation and set it to active. Once it ran from the schedule it actually worked. It was erroring when I tried to manually 'RUN ONCE'. No biggie tho, it worked. Thanks! May 4, 2018 at 20:52

You must log in to answer this question.

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