How to Update the Empty Column Records in SQL to NULL

user_14381274 0
Viewed 48
UPDATE TT 
SET DEATHDATE = NULL
FROM TEMP_TABLE TT 
WHERE LTRIM(RTRIM(DEATHDATE)) = ''

UPDATE TT 
SET CURRENTDATE= NULL
FROM TEMP_TABLE TT 
WHERE LTRIM(RTRIM(CURRENTDATE)) = ''

UPDATE TT 
SET BIRTHDATE = NULL
FROM TEMP_TABLE TT 
WHERE LTRIM(RTRIM(BIRTHDATE)) = ''

OR

UPDATE TT 
SET BIRTHDATE = NULL
FROM TEMP_TABLE TT 
WHERE DATALENGTH(BIRTHDATE) = 0

This update statement will update to NULL if the specified column records are EMPTY.

But I don't like doing this. I want to change all of the records in the table. If there are EMPTY records in that table, I want to change them to NULL.

Is that even possible? Thank you in advance

sql-server sql
1 Answers

You can combine all these into a single update, using a CASE to decide whether to overwrite the value or not.

UPDATE TT 
SET DEATHDATE   = CASE WHEN TRIM(DEATHDATE)   = '' THEN NULL ELSE DEATHDATE END,
    CURRENTDATE = CASE WHEN TRIM(CURRENTDATE) = '' THEN NULL ELSE CURRENTDATE END,
    BIRTHDATE   = CASE WHEN TRIM(BIRTHDATE)   = '' THEN NULL ELSE BIRTHDATE END
FROM TEMP_TABLE TT 
WHERE TRIM(DEATHDATE) = ''
  OR  TRIM(CURRENTDATE) = ''
  OR  TRIM(BIRTHDATE) = '';

You calso shorten the expressions to CASE WHEN TRIM(DEATHDATE) <> '' THEN DEATHDATE END etc, but I think the above is clearer.

Quite why you are storing dates in a text column is a different question. All I can say is: don't.

user_148689970
Related