How to update selective fields in SQL (leaving unchanged)?

Question

I would like to update the record with two dates, leaving the existing data intact if I do not have a new value to update.

Here is an example table entry:

id  last_foo    last_bar
--  ----------  ----------
 1  2010-05-30  2010-05-30

And the query I use:

UPDATE sampledates
   SET last_foo = @LastFoo, 
       last_bar = @LastBar
 WHERE id = @ID;

If my values ​​are null LastFoo or LastBarnull, I would like to leave the existing SQL value as it is, otherwise an update.

For example, let's say I update this entry with the following values ​​(this is C #, but any language applies):

DateTime? LastFoo = new DateTime('2010-06-04');
DateTime? LastBar = null;

I would like the entry to be as follows:

id  last_foo    last_bar
--  ----------  ----------
 1  2010-06-04  2010-05-30

I understand that I can change the query text to omit the second column if the value is null, but I was wondering if there is a way to leave the query as is and indicate that I am not changing the specified column.

Answer 1

Try

UPDATE sampledates
SET last_foo = COALESCE(@LastFoo,last_foo ), 
last_bar = COALESCE(@LastBar,last_bar )
WHERE id = @ID;

Answer 2

COALESCE:

UPDATE sampledates
SET last_foo = COALESCE(@LastFoo, last_foo),
    last_bar = COALESCE(@LastBar, last_bar)
WHERE id = @ID;

SQL Server , ISNULL COALESCE.

UPDATE sampledates
SET last_foo = ISNULL(@LastFoo, last_foo),
    last_bar = ISNULL(@LastBar, last_bar)
WHERE id = @ID;

Answer 3

Try this (this is unverified, I do not have SSMS for me right now)

UPDATE sampledates
   SET last_foo = CASE WHEN @LastFoo IS NULL THEN last_foo ELSE @LastFoo END, 
       last_bar = CASE WHEN @LastBar IS NULL THEN last_foo ELSE @LastBar END
  WHERE id = @ID;

Answer 4

You can try something like

UPDATE sampledates
SET last_foo = (case when @LastFoo IS NULL then last_foo else @LastFoo end), 
last_bar = (case when @LastBar IS NULL then last_bar else @LastBar end)
WHERE id = @ID;