SQL Query for If not null,then update or else keep the same data

Question

I am having a table which has about 17 fields. I need to perform frequent updates in this table. But the issue is each time I may be updating only a few fields. Whats the best way to write a query for updating in such a scenario? I am looking for an option in which the value gets updated only if it is not null.

For example I have four fields in database Say A,B,C,D. User updates the value of say D. All other values remains the same. So I want an update query which updates only the value of D keeping the others unaltered. SO if i put a,b and c as null and d with the value supplied by user I want to write an update query which only updates the value of d as a,b and c is null. MsSQL 2012 is my database

Answer 1

May be something like this

Update T
SET A = CASE WHEN A IS NOT NULL THEN 'Value' ELSE A END,
    B = CASE WHEN B IS NOT NULL THEN 'Value' ELSE B END,
    C = CASE WHEN C IS NOT NULL THEN 'Value' ELSE C END
    D = CASE WHEN D IS NOT NULL THEN 'Value' ELSE D END
FROM Table1 T

Answer 2

If you are calling this from an application, the easiest way is to just run the query that you want:

update t
    set d = @d
    where id = @id;

If you want a generic statement, then you can use:

update t
    set a = coalesce(@a, a),
        b = coalesce(@b, b),
        c = coalesce(@c, c),
        d = coalesce(@d, d)
    where id = @id;