1

I’m having trouble with Marketing Cloud’s “Update” option when I choose a target data extension. I want to update Tabel1 using data from the Table2. Whenever the rows for “Code” match the number 3, then Table1 should update the Language to ‘es’. When I try to do this with either a join or even a case statement, I get the error that I’m missing a primary field in the target data extension.

Would anyone have an idea of how I can accomplish this without having to move/copy data from the Table1 and use the overwrite? Feeling stuck.

**Target Data Extension (Table1)**   
    Subkey    |  Code   |  Language  |
      a1      |    1    |     en     |
      b1      |    2    |     en     |
      c1      |    3    |     en     |


**Reference Data Extension (Table2)**
     Name     |  Code   | 
    Taylor    |    1    |
    Elliot    |    2    |   
    Jose      |    3    |   

The case I tried is:

SELECT Subkey, Code, Language
    CASE
WHEN Code = 3 THEN "es"
ELSE "en"
END as Language
FROM Table1 

To use my join, I first used a query that pulled any Code = 3 from Table1 into a Table3 (uses exact same fields as Table1) and then doing this:

SELECT Subkey, Code, Language
FROM Table3
RIGHT JOIN Table1 ON Table3.Subkey = Table1.Subkey
  • Can you please post your SQL Query? – Brad Sapkota Mar 9 '18 at 2:05
  • Does your target data extension have a primary key? If so which field? – Travis Naughton Mar 9 '18 at 5:31
  • @TravisNaughton For Table1 that primary key is Subkey. – Harley Mar 9 '18 at 13:13
  • @BradSapkota I add those queries in the post. Hope it helps. Thanks! – Harley Mar 9 '18 at 13:20
  • I am confused - what is the point of table 2? You don't reference it at all. – Gortonington Mar 9 '18 at 13:47
1

I am not sure the point of Table2, but utilizing your explanation on the queries, I was able to change the 'en' to 'es' by making the queries valid.

Sample below followed by explanation:

Query 1:

SELECT Subkey, Code, 
    CASE
WHEN Code = 3 THEN 'es'
ELSE 'en'
END as Language
FROM Table1 
  1. I removed the double call for 'Language' - which created a validation error.
  2. I changed the quotes around 'en' and 'es' to single instead of double. double quotes are used when referencing columns - which made the query invalid.

Query 2:

SELECT T1.Subkey, T3.Code, T3.Language

FROM Table3 T3

RIGHT JOIN Table1 T1
ON T3.Subkey = T1.Subkey
  1. Added a shortened name to each DE, for easier referencing

  2. Referenced the correct DEs in your select statement that you wanted to pull the info from. As it was previously, it was invalid.

With these updates it worked as requested. If you update what you need table2 for, I can edit my answer to fill that in.

  • 1
    yeah. I may have gotten ahead of myself on Table2. In order to get Code = 3 into a different table, I would use that to find all records with Code = 3 and place those records into Table3. Then I was going to join Table3 to updated Table1. If I use the Case, then I'll have about 200+ lines to add in that query, which is not great. UPDATE: I created new DE with the sample code and it worked. So, I have some user error issues to work through with my real DE. Thank you for pointing my in the right direction @Gortonington ! – Harley Mar 9 '18 at 14:37
  • No problem glad to help. Feel free to post another question if you get stuck again. – Gortonington Mar 9 '18 at 14:47

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.