How do I only update null values in a column with SOQL?

Question

I have an issue with SOQL within a query activity in Salesforce Marketing Cloud Automation Studio.

I want to update null values with birth dates of users in a column named "date of birth" that includes birth dates of a ton of users with datatype = date.

I want to update this column, "date of birth", based on another column named "date of birth 2" which also includes birth dates of a ton of users but instead with datatype = string.

Thus, my goal is to be able to update only the missing date fields in the column "date of birth" leveraging data from the column "date of birth 2".

With my current very simple code (see img 1/2), I either update nothing in the data extension (when the WHERE column = NULL is added, see img 2) or I update the null values but also overwrite the existing date values in the data extension (when there's not added a WHERE column = NULL, see img 1). I wonder if it possible to simply update the null fields and let the currently filled fields stay?

Looking forward to hearing from you and please tell me if my query is unclear!

Hi Andreas, For more clarification on your problem I am guessing that you want to update __/12/2020 (date of birth) to 10/12/2020 (date of birth) because your (date of birth 2) contains 10/12/2020 for that user, right? — Asheesh Sahu
Hi Asheesh, Yes, you are correct. At the same time, I want to make sure I do not overwrite some of the already filled out fields in (date of birth) when I update the column based on (date of birth 2). Tell me if something is unclear. Thank you for your time. — Andreas Pedersen
Hi Andreas, Thanks for the clarification one more thing I want to ask is that which data type are you using is it Date or DateTime — Asheesh Sahu
Hi Asheesh, Good question. The datatype we use is Date, not DateTime. — Andreas Pedersen
Hi Andreas, Please correct me if I am wrong if your (date of birth) field is date type then how it can store blank value of either day, month or year it should throw an exception. I am assuming that your (date of birth) field either have value or not. — Asheesh Sahu

Asheesh Sahu Asheesh Sahu · Accepted Answer · 2020-10-18T10:50:00

So I think you can query on the object where your (date of birth) field is equal to null and (date of birth 2) is not equal null.

SELECT (date of birth),(date of birth 2) FROM objectName WHERE (date of birth) = NULL AND (date of birth 2) != NULL

Please make sure your (date of birth 2) field must be in YYYY-MM-DD format.

Sample code.

List<objectName> li = [SELECT (date of birth),(date of birth 2) FROM objectName WHERE (date of birth) = NULL AND (date of birth 2) != NULL];
for(objectName o : li){
 (date of birth) = Date.valueOf((date of birth2));
}
update li;

How do I only update null values in a column with SOQL?

1 Answers