SQL:只有当该值为空时,如何才能更新列上的值? [英] SQL: How can I update a value on a column only if that value is null?

查看:24
本文介绍了SQL:只有当该值为空时,如何才能更新列上的值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个 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、布朗、NULL、NULL

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,蕾娜,皮特森,NULL,金发,芬达,NULL

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

新行需要是:

121312、蕾娜、皮特森、BMW123d、金发、芬达、NULL

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

所以我的想法是这样的:

So I was thinking something along the lines of:

更新人员(PersonalID、FirstName、LastName、Car、HairColour、FavDrink, FavFood) 设置 Car = @Car (@Car 不为空), 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 不是 Null,我不能做类似更新 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

推荐答案

我为此使用了 coalesce: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:只有当该值为空时,如何才能更新列上的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆