I have the table let's say CUSTOMERS table, I would like to lookup the GENDER column against my reference table. Next, I want to insert all records (because all these records are new) into my target table, but update gender = NULL if the gender is not matched via the lookup transformation (example Gender = 'KK').

Any suggestion how I can proceed with only update specific columns then only insert into target table?

Thank you