SQL:仅当该列值为null时,如何才能更新该列上的值? [英] SQL: How can I update a value on a column only if that value is null?
问题描述
我有一个SQL问题,对于某些人来说可能是基本问题,但使我感到困惑.
I have an SQL question which may be basic to some but is confusing me.
这是表"Person"的列名的示例: PersonalID,FirstName,LastName,Car,HairColour,FavDrink,FavFood
Here is an example of column names for a table 'Person': PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood
假设我输入了该行:
121312,Rayna,Pieterson,BMW123d,Brown,NULL,NULL
121312, Rayna, Pieterson, BMW123d, Brown, NULL, 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,金发,芬达,NULL
121312, Rayna, Pieterson, NULL, Blonde, Fanta, NULL
新行必须为:
121312,Rayna,Pieterson,BMW123d,Blonde,Fanta,NULL
121312, Rayna, Pieterson, BMW123d, Blonde, Fanta, NULL
所以我在想一些类似的事情:
So I was thinking something along the lines of:
更新人员(个人ID,名字,姓氏,汽车,HairColour, FavDrink,FavFood)设置Car = @Car(其中@Car不为null),HairColour = @HairColour(其中@HairColour ...)...等.
Update Person(PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood) set Car = @Car (where @Car is not null), HairColour = @HairColour (where @HairColour...)... etc.
我唯一关心的是,我无法在查询末尾对所有条件进行分组,因为它将要求所有值具有相同的条件.如果@HairColour不为空,我是否可以做类似Update HairColour的操作
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为此请使用合并: http://msdn.microsoft.com/en-us/library/ms190349.aspx
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)
这篇关于SQL:仅当该列值为null时,如何才能更新该列上的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!