I have an SQL question which may be basic to some but is confusing me.
Here is an example of column names for a table 'Person': PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood
Let's say that I input th开发者_如何学Ce row:
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 row needs to be:
121312, Rayna, Pieterson, BMW123d, Blonde, Fanta, NULL
So I was thinking something along the lines of:
Update Person(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 can't group all the conditions at the end of the query because it will require all the values to have the same condition. Can't i do something like Update HairColour if @HairColour is not Null
Id use coalesce for this: http://msdn.microsoft.com/en-us/library/ms190349.aspx
update Person
set Car = coalesce(@Car, Car), HairColour = coalesce(@HairColour, HairColour)
The following should work:
UPDATE Person
SET Car = ISNULL(@Car, Car),
HairColour = ISNULL(@HairColour, HairColour),
...
It uses the SQL Server ISNULL function, which returns
- the first value if it is non-null,
- or, otherwise, the second value (which, in this case, is the current value of the row).
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
Set the column equal to itself with an isnull round it setting it to your parameter.
UPDATE
YourTable
SET
YourColumn = ISNULL(YourColumn, @yourParameter)
WHERE
ID = @id
精彩评论