[SalesForce] Marketing Cloud SQL to update Data Extension

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

Best Answer

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

Related Topic