Using "Update" for Target Data Extension

Question

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

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