COALESCE and updating with NULL

Question

COALESCE and updating with NULL

venkatesh aiyar

SSC Enthusiast

Points: 177
More actions
September 24, 2003 at 3:16 pm

#159891

Hi
I currently use COALESCE while updating tables so that if
the input parameter is NULL(defaulted), I keep the
existing value else update it with the passed in value.
However, There are certain rare cases where I may have to
explicitly update a column value to NULL. How do I achieve
this while still using COALESCE.
I could use flags but that defeats the purpose of using
COALESCE in the first place.
Thanks,
Venkatesh

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

I don't understand. How do you know if a NULL value is to represent keeping the existing value or changing the existing value to NULL? Aren't you asking one quanta to have two states? I suppose you could do something like this:

declare @val int, @newval varchar(10)

set @newval = NULL

set @val = 1

select CASE @newval WHEN 'NULL' THEN NULL ELSE COALESCE(@newval, @val) END

set @newval = 'NULL'

select CASE @newval WHEN 'NULL' THEN NULL ELSE COALESCE(@newval, @val) END

set @newval = 2

select CASE @newval WHEN 'NULL' THEN NULL ELSE COALESCE(@newval, @val) END

--Jonathan

Edited by - jonathan on 09/24/2003 4:07:40 PM

--Jonathan

brokenrulz SSCrazy Points: 2261 More actions · Answer 2

Try this

e.g.

update customers set

outstanding=case when coalesce(outstanding,0)=0 then null else coalesce(outstanding,0) end

He who knows others is learned but the wise one is one who knows himself.

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
Try this
e.g.
update customers set
outstanding=case when coalesce(outstanding,0)=0 then null else coalesce(outstanding,0) end

Isn't that equivalent to just NULLIF(Outstanding, 0)?

How does this relate to the OP's question?

--Jonathan

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

Something in another thread got me thinking about this. If you want no values passed to mean no update to that column, but allow NULLs to be passed, something like this may work:

CREATE PROC p_UpdateTest

@id int,

@NewCol1Val int = -2147483648,

@NewCol2val varchar(10) = 0x00

AS

SET NOCOUNT ON

UPDATE Test SET

Col1 = CASE @NewCol1Val WHEN -2147483648 THEN Col1 ELSE @NewCol1Val END,

Col2 = CASE @NewCol2Val WHEN CHAR(0) THEN Col2 ELSE @NewCol2val END

WHERE id = @id

You wouldn't have to deal with flags, but it will not update columns with no passed values but update the columns with any passed values, including NULLs. Choose the SP's default values to be those that wouldn't naturally occur.

I trust you're not using methods like COALESCE() or the above to do mass updates...

--Jonathan