[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

sql case if not null... i need to perform an update if a value is not null

I have a system that that passes values from tablets to a sql server.  In that processes the values are passed to variables.  if the user did not change a value then the system passes a null....

I need to be able to perform a check against the variable to see if it is null and then update if it is not..
I had this working using IF statements but cannot get it to work with case (this is the first time I have used case - just learning)

I decided to switch to case because i thought I could simplify things (there are more cases, but i managed to get stuck on the first :)

Here is what i have, and it updates the table if the value is null (and of course it it is not)
thanks for your help in advance

declare @How_many_on_shelf float
declare @InventoryID as int
set @How_many_on_shelf = null
set @InventoryID = 2

Update OtherItem	
set Onshelf = Case
	When @How_many_on_shelf is not null then @How_many_on_shelf	
end
 Where ItemId = @InventoryID
		

Open in new window

0
David Modugno
Asked:
David Modugno
  • 12
  • 6
1 Solution
 
Anthony PerkinsCommented:
Obviously you would not do this for real, but just as a learning experience try it this way:
DECLARE @How_many_on_shelf float
DECLARE @InventoryID AS int
SET @How_many_on_shelf = NULL
SET @InventoryID = 2

UPDATE  OtherItem
SET     Onshelf = CASE WHEN @How_many_on_shelf IS NOT NULL THEN @How_many_on_shelf
                       ELSE Onshelf
                  END
WHERE   ItemId = @InventoryID

Open in new window

0
 
David ModugnoAuthor Commented:
it still did not work... again i am just learning, so i have no idea why i would not do this for real.. let me ask it another way... I am trying to find the best solution for the following...
I have an ipad app that has a form filled out for inventory
When they fill out the form if they leave something blank then the value can not change on the backend
when the app is submitted it uploads for processing
when processing it loops through all answers even the blank one (i have no control over this)
the blank ones are passed as Null's
there are data fields for every inventory item that can be updated
I'm trying to get it to skip the nulls so that those values stay as they are

its likely that I am over complicating this in my head... i used an if statement on a form that only had one data field works great... it looks like this
IF @Item_Count Is Not Null 
begin   
   if exists (select * from TruckInventory with (updlock,serializable) where TruckNo =  @Select_Truck_or_Trailer and InventoryId = @ItemID)
     begin
       update TruckInventory
       Set Actual=@Item_Count
       Where TruckNo =  @Select_Truck_or_Trailer and InventoryId = @ItemID
     end
   else
     begin
      insert INTO TruckInventory (TruckNo,InventoryId,Actual)
       values (@Select_Truck_or_Trailer,@ItemID,@Item_Count)
      end
end

Open in new window


but I keep ending up with a bunch of if statments and still have issues..
on the one with two fields i see the following posabilities

(i will call the variables Num1 and Num2 to keep it simple)
num1 and two could both be Null (do nothing)
num1 and num2 could both have a value (update or insert both)
num1 null and num2 has a value (skip num1, update or insert num2)
nu1 has a value and num2 null (update or insert num1 skip num2)

I just am not sure how to set this up without it being really ugly :)

any help would be great... thanks for dealing with the newbie
0
 
David ModugnoAuthor Commented:
the second to last bullet is ... there are two data fields
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Anthony PerkinsCommented:
it still did not work.
I am sorry but that tells me nothing.  Do you
1. Get an error message? If so, what is it.
2. Do you get the wrong result?  If so what was it and what was the expected result
3. Something else.

>>so i have no idea why i would not do this for real.<<
Because if it was that trivial you would use a WHERE clause.

(i will call the variables Num1 and Num2 to keep it simple)
I am not following you.  The question has now morphed from a simple UPDATE statement into a question with two conditions that could require an INSERT or an UPDATE.
I suggest you post the schema to the table.  Some sample data,  A simple UPDATE that "works" and what enhancements you need done to it.
0
 
David ModugnoAuthor Commented:
I originally left out the insert update portion as if i can get one to work I can get them both to work..
I ran the following tests with your query..
ran it with a value of 5 and it updated as expectedafter running with value of 5
0
 
David ModugnoAuthor Commented:
clicked the wrong button - submitted before i was done
0
 
David ModugnoAuthor Commented:
if you notice in the pic that is the result if the value is 5..
now with null

well .... not sure what i did last night but now it worked...
from here I need to add the other cases...
if you look at the pic you will see the other column that needs updated but not changed if the value is null

is there a better way  to accomplish this

thanks for your help
0
 
Anthony PerkinsCommented:
is there a better way  to accomplish this
What I meant was that if that was all you wanted to do in the UPDATE statement than this would be a better approach:
UPDATE  OtherItem
SET     Onshelf = @How_many_on_shelf
WHERE   ItemId = @InventoryID
		AND @How_many_on_shelf IS NOT null

Open in new window

But without knowing the full scope it is difficult to know what is best.
0
 
David ModugnoAuthor Commented:
sure.. but I don't want to update if the value is null for either....
I only want to update if the value is not null

so... as above
if both null ... do nothing
if one is null just update that one
if they both are not null update them both
0
 
David ModugnoAuthor Commented:
So I can do this, but is there a better way.. something more simplified
IF (@How_many_on_shelf  Is Not Null)
begin 
   if exists (select * from OtherItem with (updlock,serializable) where ItemId = @InventoryID)
     begin
       UPDATE  OtherItem
       SET     Onshelf = @How_many_on_shelf
       WHERE   ItemId = @InventoryID
     end
   else
     begin
      insert INTO OtherItem (ItemId,Onshelf)
       values (@InventoryID,@How_many_on_shelf)	  
     end
end


IF (@How_many_not_of_shelf_other  Is Not Null)
begin 
   if exists (select * from OtherItem with (updlock,serializable) where ItemId = @InventoryID)
     begin
       UPDATE  OtherItem
       SET     Other = @How_many_not_of_shelf_other
       WHERE   ItemId = @InventoryID
     end
   else
     begin
      insert INTO OtherItem (ItemId,Other)
       values (@InventoryID,@How_many_not_of_shelf_other)	  
     end
end

Open in new window

0
 
Anthony PerkinsCommented:
You could do it this way:
IF COALESCE(@How_many_on_shelf, @How_many_not_of_shelf_other) IS NOT NULL
    BEGIN 
        IF EXISTS ( SELECT  *
                    FROM    OtherItem WITH (UPDLOCK, SERIALIZABLE)
                    WHERE   ItemId = @InventoryID )
            BEGIN
                UPDATE  OtherItem
                SET     Onshelf = ISNULL(@How_many_on_shelf, Onshelf),
                        Other = ISNULL(@How_many_not_of_shelf_other, Other)
                WHERE   ItemId = @InventoryID
            END
        ELSE
            BEGIN
                INSERT  INTO OtherItem
                        (ItemId,
                         Onshelf,
                         Other
                        )
                VALUES  (@InventoryID,
                         @How_many_on_shelf,
                         @How_many_not_of_shelf_other
                        )	  
            END
    END

Open in new window

However, that would produce only one INSERT (compared to two in your code) when both values are not null.
0
 
David ModugnoAuthor Commented:
that is perfect.. could you explain Coalesce?

Thanks so much for your help.. i just want to understand it so i can use it in the future..
thanks
0
 
David ModugnoAuthor Commented:
I think i figured it out .... so because you used Coalesce in an if statement... if either had a value other than null (Coalesce looks for the first non null) it would begin the next steps.... where if both are null .... it would not return a value and end....
thanks again .. I I like that function :)
0
 
Anthony PerkinsCommented:
Glad you figured it out.

You did however see my comment: However, that would produce only one INSERT (compared to two in your code) when both values are not null., right?
0
 
David ModugnoAuthor Commented:
yes
0
 
David ModugnoAuthor Commented:
but I don't think that is true.... in your code the first check is just to return the first non -null
then we jump into the begin ... if there is a non - null

once there... the code does a check to see if any record exists with that id.. if not move to insert

the insert then performs based on the two variables ....

INSERT  INTO OtherItem
                        (ItemId,
                         Onshelf,
                         Other
                        )
                VALUES  (@InventoryID,
                         @How_many_on_shelf,
                         @How_many_not_of_shelf_other
                        )	  

Open in new window

0
 
Anthony PerkinsCommented:
the insert then performs based on the two variables ....
That is correct in the code I posted.  So therefore only one INSERT.

Now that I re-read your code, I can see that there can never be two INSERTs as I had previously thought:  If the first variable is not NULL and there is an INSERT, then it will be there for the second case.

So they should be fine.
0
 
David ModugnoAuthor Commented:
thanks again for the help.. have a good one
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now