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

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  September 24, 2003 at 4:02 pm

  #475685

  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

  September 24, 2003 at 11:20 pm

  #475717

  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.

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

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  September 25, 2003 at 12:54 pm

  #475861

  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

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  September 25, 2003 at 1:01 pm

  #475863

  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

  
  
  --Jonathan