Null value problem

Hi
I sent the following query
'Update TableA SET F1 = F1 + F2'
and where the value of F2 was Null then the value of F1 became Null even
where the original value of F1 was NOT Null
Is that the way it suppose to work?
Thank you,
Samuel
Samuel Shulman wrote:

>Hi
>I sent the following query
>'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
>where the original value of F1 was NOT Null
>Is that the way it suppose to work?
>
>
Yes.
If you want to leave the value of F1 unchanged (assuming
F1 and F2 are numeric types), try
UPDATE TableA SET F1 = F1 + COALESCE(F2,0)
or
UPDATE TableA SET F1 = F1 + F2
WHERE F2 IS NOT NULL
Steve Kass
Drew University

>Thank you,
>Samuel
>
>|||Yes NULL + any other value = NULL (depending on CONCAT_NULL_YIELDS_NULL
setting of course)
select NULL + 'ABC' -- this will return NULL
do this instead for strings
Update TableA SET F1 = coalesce(F1,'') + coalesce(F2,'') or
for numeric values
Update TableA SET F1 = coalesce(F1,0') + coalesce(F2,0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Samuel Shulman wrote:
> Hi
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
> Thank you,
> Samuel|||What data types are using? Are you trying to concatenate or ADD
Post a "real " example.
If you were trying to add, then NULL + 1 , will result in NULL , you could
get around the problem by using the ISNULL function.
Therefore you could do something like 'Update TableA SET F1 = ISNULL(F1,0) +
ISNULL(F2,0)'
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OaAFwvKiGHA.4080@.TK2MSFTNGP03.phx.gbl...
> Hi
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
> Thank you,
> Samuel
>|||Samuel Shulman (samuel.shulman@.ntlworld.com) writes:
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
Yes. NULL is an unknown value, and it doesn't become less unkonown because
you add it with a known value.
If you know that in the specific context that you are working in that a
NULL is the same thing as 0, you can say
UPDATE TableA SET F1 = F1 + coalesce(F2, 0)
The function coalesce() takes a list of expressions as argument, and returns
the the first non-NULL value in the list, or NULL if all are NULL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes coalesce() is the way to go.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Samuel Shulman" wrote:

> Hi
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
> Thank you,
> Samuel
>
>|||Just in addition to what others have noted...
If NULL really means 0 or '', then make your column not null and define the
appropriate default.
NULLs have a behavior all their own, and you either have to prevent the NULL
values up front, or code for them after the fact.
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OaAFwvKiGHA.4080@.TK2MSFTNGP03.phx.gbl...
> Hi
> I sent the following query
> 'Update TableA SET F1 = F1 + F2'
> and where the value of F2 was Null then the value of F1 became Null even
> where the original value of F1 was NOT Null
> Is that the way it suppose to work?
> Thank you,
> Samuel
>