Update wit filter condition for multiple columns in sql server

Question

update columns based on effectivedate filter.

If effectivedate is null then update the column1,column2,column3

from table

else

If effectivedate is not null then update the column1,column2,column3

from table

how to write this condition using sql code

Answer 1

Hm, you are updating the same columns in both cases. The order is different, but that does not matter.

But say that you want to update col1 when effectiveDate is NULL and update col2 is it is non-NULL. This is how you can do it:

UPDATE tbl
SET  col1 = CASE WHEN effectiveDate IS NULL THEN <some new value> ELSE col1 END,
     col2 = CASE WHEN effectiveDate IS NULL THEN col2 ELSE <some new value> END
WHERE ...

Answer 2

Hi @Naresh y

Your description confuses me.

If column1, column2, and column3 are updated at the same time. All you need is a simple update statement like this.

update table 
set column1 = <some new value>,column2 = <some new value>,column3 =<some new value> 
where effectivedate is (not) null;

If you need columns 1, 2, and 3 to update differently depending on whether the effectiveDate is null or not, you can try Erland's method.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.