SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update records based on null values

    I am looking to update columns ONLY when they are null, e.g.:

    UPDATE [AppUser] SET [FirstName] = @FirstName, [LAstName] = @LastName
    WHERE AppUserId = 1

    I need to check whether the [FirstName] is NULL, if so update using the passed in variable @FirstName, the same goes for [LastName].

    How did I write the T SQL to do this?

    I am using MS SQL 2005 and stored procedures...

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    update AppUser 
         set FirstName = coalesce(FirstName,@FirstName), 
              LAstName = coalesce(LarsName,@LastName)
     where AppUserId = 1

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This works well - many thanks.... is there a way of doing this for empty string values as well?

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Worked it out, I am using this:

    titlename = coalesce(nullif(TitleName,''),'Mr')

    Any problems with this you can envisage?


Bookmarks

Posting Permissions

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