Results 1 to 4 of 4

Thread: update query

  1. #1
    Join Date
    Aug 2006
    Posts
    39

    Unanswered: update query

    hello,
    This is the update query that I have created.
    Let's say @UnderlyingIndexID is to be set to null instead of an existing value of 10.
    I think this query does not allow for such an update.
    How can this query be altered to do so?
    Thanks

    update
    tblTest
    set
    IsActive = isnull(@IsActive, IsActive),
    UnderlyingIndexID = isnull(@UnderlyingIndexID, UnderlyingIndexID),
    LastUpdatedUser = @ActiveUser,
    LastUpdatedDate = getdate()
    where
    IndexID = @IndexID

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Code:
    UnderlyingIndexID = @UnderlyingIndexID
    UnderlyingIndexID will now get the value that is assigned to @UnderlyingIndexID, even if it is NULL.
    Last edited by Lexiflex; 01-16-07 at 09:00.

  3. #3
    Join Date
    Jan 2007
    Posts
    3
    If you want to to retain value of UnderlyingIndexId when @UnderlyingIndexId is NULL, then you can try using COALESCE function.
    i.e.

    Update
    tblTest
    Set
    IsActive = isnull(@IsActive, IsActive),
    UnderlyingIndexID = COALESCE(@UnderlyingIndexID, UnderlyingIndexID),
    LastUpdatedUser = @ActiveUser,
    LastUpdatedDate = getdate()
    Where
    IndexID = @IndexID

    See if this is what you want.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    isnull() is functionally identical to coalesce() when evaluating a single value, which is beside the point since that's not what the OP is asking.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •