SQL: how can I update a value in a column only if that value is null?

Question

I have a SQL question that may be basic to some, but it bothers me.

Here is an example of the column names for the Man table: PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood

Let's say that I enter the line:

121312, Rayna, Pieterson, BMW123d, Brown, NULL, NULL

Now I want to update the values ​​for this person, but only if the new value is not null, Update:

121312, Rayna, Pieterson, NULL, Blonde, Fanta, NULL

The new line should be:

121312, Rayna, Pieterson, BMW123d, Blonde, Fanta, NULL

So, I was thinking something like:

Update character (PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood) set Car = @Car (where @Car is not null), HairColour = @HairColour (where @HairColour ...) ... etc .

My only concern is that I cannot group all the conditions at the end of the query, because all of these values ​​will require the same conditions. Could I do something like Update HairColour if @HairColour is not Null

Answer 1

To use Id, the union is used: http://msdn.microsoft.com/en-us/library/ms190349.aspx

update Person set Car = coalesce(@Car, Car), HairColour = coalesce(@HairColour, HairColour) 

Answer 2

The following should work:

 UPDATE Person SET Car = ISNULL(@Car, Car), HairColour = ISNULL(@HairColour, HairColour), ... 

It uses the SQL function ISNULL , which returns

• first value, if it is not null,
• or, otherwise, the second value (which in this case is the current value of the string).

Answer 3

You can use the isnull function:

 update Person set Car = isnull(@Car, Car), HairColour = isnull(@HairColour, HairColour), FavDrink = isnull(@FavDrink, FavDrink), FavFood = isnull(@FavFood, FavFood) where PersonalID = @PersonalID 

Answer 4

Set the column equal to itself using isnull round by setting it to your parameter.

 UPDATE YourTable SET YourColumn = ISNULL(YourColumn, @yourParameter) WHERE ID = @id