MySQL SQL：只有当该值为空时，我才能更新列上的值吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2675968/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: How can I update a value on a column only if that value is null?
提问by Tamara JQ
I have an SQL question which may be basic to some but is confusing me.
我有一个 SQL 问题，这对某些人来说可能是基本的，但让我感到困惑。
Here is an example of column names for a table 'Person': PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood
以下是表“Person”的列名示例：PersonalID、FirstName、LastName、Car、HairColour、FavDrink、FavFood
Let's say that I input the row:
假设我输入了行：
121312, Rayna, Pieterson, BMW123d, Brown, 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, Rayna, Pieterson, NULL, Blonde, Fanta, NULL
121312, Rayna, Pieterson, NULL, 金发女郎, 芬达, NULL
The new row needs to be:
新行需要是：
121312, Rayna, Pieterson, BMW123d, Blonde, Fanta, NULL
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.
更新 Person(PersonalID, FirstName, LastName, Car, HairColour, FavDrink, FavFood) 设置 Car = @Car（@Car 不为空），HairColour = @HairColour（@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
我唯一担心的是我无法在查询结束时对所有条件进行分组，因为它将要求所有值都具有相同的条件。如果@HairColour 不为空，我就不能做一些更新 HairColour 之类的事情吗？
回答by Fabian
Id use coalesce for this: http://msdn.microsoft.com/en-us/library/ms190349.aspx
我为此使用合并：http: //msdn.microsoft.com/en-us/library/ms190349.aspx
update Person
set Car = coalesce(@Car, Car), HairColour = coalesce(@HairColour, HairColour)
回答by Heinzi
The following should work:
以下应该工作：
UPDATE Person
SET Car = ISNULL(@Car, Car),
HairColour = ISNULL(@HairColour, HairColour),
...
It uses the SQL Server ISNULLfunction, which returns
它使用 SQL Server ISNULL函数，该函数返回
- the first value if it is non-null,
- or, otherwise, the second value (which, in this case, is the current value of the row).
- 第一个值，如果它是非空的，
- 否则，第二个值（在这种情况下，是该行的当前值）。
回答by Guffa
You can use the isnullfunction:
您可以使用该isnull功能：
update Person
set
Car = isnull(@Car, Car),
HairColour = isnull(@HairColour, HairColour),
FavDrink = isnull(@FavDrink, FavDrink),
FavFood = isnull(@FavFood, FavFood)
where PersonalID = @PersonalID
回答by Robin Day
Set the column equal to itself with an isnull round it setting it to your parameter.
使用 isnull 将列设置为等于自身，将其设置为您的参数。
UPDATE
YourTable
SET
YourColumn = ISNULL(YourColumn, @yourParameter)
WHERE
ID = @id
