0

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.

SELECT 
a.order_number as [TRANSACTION_KEY]
FROM IGO_PURCHASES AS a
INNER JOIN TEST_PI AS c
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? – garek007 May 3 '18 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. – Jason Taylor May 3 '18 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. – Tom Callahan May 3 '18 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. – Jason Taylor May 4 '18 at 12:53
  • can you post an example of your table structure for each table? – garek007 May 4 '18 at 13:04
0

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.

SELECT 
order_number as [TRANSACTION_KEY]
FROM IGO_PURCHASES

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. – Jason Taylor May 4 '18 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. – Gortonington May 4 '18 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. – Jason Taylor May 4 '18 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 – Gortonington May 4 '18 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! – Jason Taylor May 4 '18 at 20:52

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.